Home > Excel > HOWTO: Stupid Excel Tricks to make your life a little nicer.

HOWTO: Stupid Excel Tricks to make your life a little nicer.

I have been doing a few things in Excel lately for a couple of projects and thought I’d jot down some of the simple things that can help make your work a little nicer – or just help me cheat later when I need to remember how the heck I did that last time.

1) Converting case.

clip_image001

I’m sure there’s some fancy way to convert an entire column on its own. But what I’ve found works well is:

a) Insert a new column next to the first – eg: B above

b) Use the formula “=UPPER(A3)” which will convert to upper. Note that “=PROPER” and “=LOWER” also exist – sometimes you want to convert to all lower case, or you want to properly case usernames like “Avram Woroch” or “AVRAM WOROCH” or “avram woroch”

clip_image002

c) Paste the formula into all of ColB, using PASTE VALUE so you don’t paste formatting, etc.

d) Copy all of ColB

e) PASTE VALUE into ColA. Of course, if you “PASTE” you’ll paste the formula, and ColB’s formula will have a circular reference, etc.

f) Delete ColB as it’s no longer required.

I’d love to find a way to just highlight ColA, right click and say “Make UPPER”. But I can understand why it might not exist.

 

2) Conditional Formatting based on multiple values

clip_image003

Conditionally formatting A cell based on a value or formula is easy. But doing it on an entire row, based on multiple values took me a little digging. In my use case, I wanted to highlight the row if all 3 software products were upgraded to the expected version, to indicate that server was complete.

a) Highlight the area you want to format – for a sample row. We’ll handle the rest later.

clip_image004

b) Click CONDITIONAL FORMATTING, NEW RULE:

clip_image005

c) Select USE A FORMULA:

clip_image006

d) In my case, I used the following “=AND” formula:

=AND($K2=”7.0.3.6341″, $M2=”4.1.4348.1209″, $O2=”6.0.4649.1236″)

This is indicating that the value in K should be 7.0.3.6341, M should be 4.1.4348.1209, and O should be 6.0.4649.1236. Only if ALL 3 are valid, does the rule match. Then set your formatting:

clip_image007

You end up with the above. Click OK.

e) Next, we need to apply it to the entire range of data. Select any cell in the above area – note we’re doing ROW2 in the example. Click CONDITIONAL FORMATTING and MANAGE RULES:

clip_image008

clip_image009

Here you can see the conditional rules that apply to this cell. In the example above, it only applies to ColA-Q and Row19-20. We want it to apply to =$A$2:$Q$50 to cover the 49 records in the sheet. Click OK.

NOTE: You can’t use cursor keys in this box to move around, so you have to be pretty deliberate about your selection. You can also use the SELECTION button to the right of the box to graphically select the range.

 

3) Looking up data in another sheet.

Let’s assume you have the following situation:

· One master sheet that is your presentation

· One or more data sheets that is your imported data – such as a list of systems that have a software package installed and a particular version, exported from some application.

· You want to lookup the data from the imported data sheet based on the server name in your master sheet, if it exists.

Easy enough – VLOOKUP to the rescue.

a) Create a new tab, and pay attention to its name. For NetApp Host Utilities for Windows I called the tape “HOST_UTILS_POST” as this was the sheet from AFTER I did upgrades. Import your data, likely a copy and paste.

b) Note the formatting of your data:

clip_image010

Your SERVERNAME is in ColA, and your version is in ColE – E is the 5th column. We need to know the column number, as a reference from the server name.

c) We’re going to use the following VLOOKUP formula:

clip_image011

Let’s break this down:

$A13 is the column in THIS sheet, that we want to lookup based on – eg: SERVERNAME

HOST_UTILS_POST! is the SHEET we’re doing the lookup in

!$A$2:$F$100 is the RANGE on that sheet we’re looking – ColA-F, and Row2-100.

,5 is the REFERENCE column we’re looking for. This is important as the INDEX record we’re looking for (SERVERNAME) *MUST* be the first field in this selection. This means that if the data sheet has the SERVERNAME in ColC, we’d not want to use ColA-F as a reference, but ColC-F. VLOOKUP only looks up based on the FIRST column to find it’s match

,0 is FALSE, which means it is not looking for an exact match.

clip_image012

As you can see from this example, it works fine. Except…. We get a ton of ugly “#N/A”’s if the value we’re looking for isn’t found in the other list. See this process works good if you’re looking to find out WHAT VERSION is installed, and report it. But it’s not so good at reporting IF ANY version is installed. Thus, when no match is found, you get the “#N/A”

 

4) Using IFERROR to handle errors.

So we can fix the above by using IFERROR:

a) What we do is we wrap the same formula in =IFERROR(xxxxxxxx),”<do something>”) as shown:

clip_image013

What this example does is that IF VLOOKUP output presents an error, then inside the quotes following is what it will do. As we use a double quote, it simply leaves the cell blank. But we could, for example, make it indicate “MISSING” as such:

b) Let’s change it to “MISSING”, omitting the ! as this could be seen as a reference to a sheet:

clip_image014

What you’d end up with then is:

clip_image015

Why would you want to use “MISSING” vs “”? Perhaps you want to do some Conditional Formatting, and don’t want to do it based on “IF CELL IS EMPTY” as this would potentially flag every unused cell in the sheet. Using this method you could do a Conditional Format of “IF=MISSING, then REDTEXT” or similar.

Advertisements
Categories: Excel
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: