Better charts. Smarter lookups. Your Excel spreadsheets made better.

You'll be able to easily sort your chart troubles, tame the lookup error monster and slick up your forms with these 10 Excel tips.

 

 
 
 
excel 2016 mac icon
Microsoft

Become a spreadsheet master

Are you the nerd at the holiday party sequestered at the kitchen table, surrounded by empties, trading tips with the other spreadsheet junkie in town? You grouse over VLOOKUP, high five each other’s complex formula strings, and bemoan chart failures that made you scream. But this is the most fun you’ve had since you got your Ham Radio License.

When it comes to charts, we’re here to help. These tips will sort your troubles, tame the lookup error monster and really slick up your forms.

slide 2 wrangle data with filters
Jim Desmond

Wrangle data with filters

Are you scrolling through spreadsheets chasing data and destroying your eyes? Excel's Filter feature offers a better way.

Let’s say you want to see records for customers who spent more than $100,000 but less than $300,000 during a two-week period in March. Or maybe you are looking for all the invoices you sent one customer. Filter is your tool!

Here’s how:

Select the header row at the top of your data range, click Data and the Filter icon in the ribbon.

A down arrow control appears on the right edge of each cell in the top row.

Click on one of those arrows. A menu appears with a drop-down listing every value for cells in that column. You can select or deselect these using the checkboxes. These filters are context-savvy. So they let you sort or filter by name, date, number, text, or other attributes, depending on the type of data in the column.

Have fun! Display only records with customers whose names start with "O'" like O'Brien and O’Neill. Sort by color. You can even select and copy the visible records to another sheet for close inspection. To remove a filter, click the drop-down arrow, and click Clear Filter.

slide 3 better forms
Jim Desmond

Easier, better forms

Excel can create elegant forms and documents that link to back-end data. And you can use it to build handy grids to work up things like floor plans and illustrations. But its mismatched units for cell height and width make this confusing work.

There is a simple workaround: Click View > Page Layout.

You now see a spreadsheet view with margins, rulers and a placeholder for header and footer text. But look closely! In Page Layout view, the units of measure for cell size have changed to inches. So weird!

But it’s awesome. Try this:

Select the spreadsheet, right-click in the column header area and click Column Width. Enter the width in inches (.25 mimics standard quad-style graph paper). Right-click the row header, click Row Height and enter the same number. Boom! your cells are perfect squares.

Wanna get your metric on? With the sheet open, click File > Excel Options and click the Advanced item. Scroll down to the Display area and select Centimeters or Millimeters from the Ruler units drop-down list.

Don’t’ freak out when you return to Normal view. Excel shifts back to mismatched units. A column width of .25 inch is 2.43 units and your row height is now 18 units.

slide 4 hellovlookup
Jim Desmond

Hello VLOOKUP

Got a big spreadsheet full of, say, customer data? Looking for a way to find a phone number, email address or some other specific answer buried in there?

VLOOKUP is an often-used way to quickly hunt down just this sort of thing.

Let’s say that the column all the way to the left (A) has the customer numbers. You are looking for the phone number associated with one customer number. Column H holds phone numbers. VLOOKUP can find it for you.

In the cell you want the answer displayed, type: =VLOOKUP([the customer number], the range where you want to look for the value, [the column number for H, which in this scenario is 8].

VLOOKUP will go to the row with the customer number you gave it (within the range you typed or highlighted) and return the value in that row that is eight columns to the right.

If all goes well, it found your phone number. But it is fussy. If your attempt failed, proceed to the next slide for solutions.

slide 5 vlookup
Jim Desmond

VLOOKUP: They call it a trap!

VLOOKUP is the Excel power user's weapon of choice when it comes to plucking data (say, a phone number) from a row of customer data by telling Excel what to look for (say, an account number). But it is so finicky that it will make you cry.

  • It insists that its lookup column be the left most in your table. Ask it to perform a lookup against a column in the middle and it spits back an error.
  • The syntax is brittle. You tell it where to find data by entering the offset (the number of columns over) from the lookup column. But add columns to your table, and you’ll change the offset and send VLOOKUP hunting in the wrong cell!
  • The VLOOKUP default returns values based on a partial match, which can be annoying. Accidentally type "Davison" instead of "Davidson" and it spits out a value related to "Davis." To avoid false positives, enter "0" or FALSE as the final argument in a VLOOKUP function to force exact matches. Then when you type “Davis” by accident, it gives you a #N/A error.
  • It’s a resource hog, insisting Excel load the entire array just to search a couple of columns.

Want a better way? Go to the next slide.

slide 6 indexmatch
Jim Desmond

The joys of INDEX MATCH

Want a better way to pluck data out of spreadsheets? Try the INDEX and MATCH functions.

This approach is easy. And it completely avoids the column formatting problems that are so frustrating with VLOOKUP. With INDEX/MATCH, you stipulate the column range to search, the value you want to look up and the range you want to look in.

Here's an example.

We are trying to match the ID number 117 (in the left column) with that person’s last name, found in Column C (the third column.) We are searching in the range of cells in the column C1 through C100.

That query, using INDEX MATCH, looks like this:

=INDEX(C2:C100,MATCH(117, A2:A100))

For reference, that query -- using VLOOKUP -- looks like this:

=VLOOKUP(117,A2:E100,3)

The query is a bit more complex. But it’s much more versatile. You can use any column as your INDEX range.

Want a last name and only have an email address to search on? Drat! Email addresses are in Column F. With VLOOKUP you'd have to create a new table and put the email info in the left-most column. With INDEX MATCH, just enter the email address you are looking for and a range containing the email data, like this:

=INDEX(C2:C100,MATCH("Dexter@contoso.com", F2:F100))

slide 7 groups
Jim Desmond

Structure large sheets with outline groups

Excel's Outline mode is a great way to roll up columns or rows of data – called Outline Groups – to hide the details and show off subtotals or other high-level data.

You can tweak this feature to organize spreadsheets. Start by choosing the Data menu, then click the expansion tick in the Outline box. In the dialog box, uncheck Summary rows below detail and Summary columns to the right of detail and click OK.

Now you can set up a group of columns that roll out of a header column, which in this example will be column D. Select columns E through K and click the Data menu item. Click the Group icon in the ribbon and a minus button icon appears with a rule that extends above the columns you selected. Click the minus icon and columns E through K roll up, leaving column D visible, followed by Column L.

Imagine a customer spreadsheet where those columns E through K contain address data, and where another Outline Group contains contact information like phone numbers and email. Now you can expand and collapse all that detail as needed.

slide 8 sheetname
Jim Desmond

Take advantage of sheet names

Excel lets you rename sheets (by double-clicking the sheet tabs), so they can have useful names like December or Q1 Sales or Customer Data. Have you ever wanted to display that name in your spreadsheet?

This is a clever trick that requires a little nerdy detective work.

The introspective CELL function returns some properties of the active spreadsheet. For instance, the formula =CELL("filename") displays the file path, name and active sheet, like so: C:\Tracking\[2017Sales.xlsx]2017 Q1 Sales.

Neat. But to display just the sheet name, use the SEARCH function to search the CELL function’s results and isolate the unique character that always sits right before the sheet name itself – the right bracket ("]").

Here is the formula to enter to locate that:

=SEARCH("]",CELL("filename"))

Enter it in any cell in a sheet. The cell will display the number of characters in from the left where the bracket sits (in this case, 28). From there, add a MID function to pluck out the text at that spot, and add a "+1" fudge factor so you don't pick up the bracket.

The final formula looks like this:

=MID(CELL("filename",$A$1),SEARCH("]",CELL("filename",$A$1),1)+1,32)

The $A$1 there ensures the value is specific to the active sheet. Leave it out and it will display the name of whatever sheet was active when you last updated the spreadsheet. That final 32 is the maximum number of characters a sheet name can have in Excel.

slide 9 formulas to text
Jim Desmond

Formulas that turn text into data

Ever get a line of data you want to plot in Excel but it’s too much bother to edit? Formulas are the answer.

I copy and paste transactions from my credit card statements into Excel and use formulas to extract the date into their own columns so I can sort, filter, and group purchases. Here's a typical entry:

01/19 AMAZON MKTPLACE PMTS AMZN.COM/BILL WA 27.68

For the date, I enter =LEFT(A2,5) in the cell next to the entry. Every date item is five letters in length, so this simple formula grabs the first five letters in the cell and displays it – in this case "01/19".

You can add a bit of savvy to that extracted date, by using concatenation to add a year value, like this: =LEFT(A2,5)&"/2017"

Now the cell displays 01/19/2017. Finally, apply the DATEVALUE function so Excel treats the cell contents as date information: =DATEVALUE(LEFT(A2,5)&"/2017").

slide 10 formula price
Jim Desmond

Formula to grab text from the end

Now for a tougher trick – pulling price information from the end of a cell that contains this string:

01/19 AMAZON MKTPLACE PMTS AMZN.COM/BILL WA 27.68

Purchases can range from single dollar amounts (four characters, with the decimal) up to the thousands (seven or potentially even eight characters), so you can't just use the RIGHT function to pull the last four or five characters. But if you know the position of the final space in the string, Excel can deduce the number of characters in the price.

Use the LEN function to get the cell contents length in characters (49) and SUBSTITUTE to get the content length with all spaces removed (43), like this:

=LEN($A2)-LEN(SUBSTITUTE($A2," ",""))

The result (6) is how many spaces in that cell. Using SUBSTITUTE again, replace that 6th and final space before the price with a unique character (I use the pipe "|" symbol), thusly: =SUBSTITUTE($A2," ","|",6).

Now, count the number of characters until the pipe symbol, using the FIND function, like so: =FIND("|",$A4)

This formula returns 44. Subtract that from the total length of the cell contents (49) and you get 5, which is the number of characters in 27.68. The final formula to isolate the price value is:

=RIGHT($A4,(LEN($A4)-FIND("|",$A4)))

Now I can sort, filter and use conditional formatting to get a better handle on my purchase habits and patterns.

slide 11 name manager
Jim Desmond

Show your work

There's an odd feeling of accomplishment that comes from building a lengthy mega-formula and watching it perform a multi-step transformation in a single cell. There is also visual elegance that comes from reduced clutter. But complex formulas can be opaque and vulnerable. A single overwritten cell or careless copy and paste can haul down the entire edifice, producing a wave of error messages or, worse, silently passing bad data.

To avoid this, take the time to show your work. Step transformations out across multiple cells, rather than nesting them together in one. That way edits and fixes can be done efficiently. And provide notes, using Excel comments, to explain what a formula does and what data it relies on. This helps downstream technicians – and you at a later date – when it comes time to inspect, tweak, update, or replace your work.

Make arrays and formulas more accessible and robust by using well-thought-out range names that improve organization and won't change or increment if a row gets inserted. Use “SheetName.RangeName” convention so ranges are easy to track down in multisheet spreadsheets.

=INDEX(C2:C1000,MATCH("Charlie@contoso.com",F2:F1000,0))

Becomes this:

=INDEX(cust.lastname,MATCH("Charlie@contoso.com",cust.email,0))

Get familiar also with the Name Manager, tucked away in the Formula section of the user interface ribbon, so you can edit, rename, and delete named ranges easily as your spreadsheet structure evolves.

slide 12 in cell chart
Jim Desmond

Declutter in-cell charts

Conditional formatting is a wonderful, if underused, tool for spreadsheet management, letting you create visual cues based on values and properties in cells.

A simple version of this is Excel's handy in-cell Data Bars feature. Select a range of values – say, daily revenues for a store – then click the Conditional Formatting button in the Styles group under the Home tab in the ribbon panel. Select one of the options there, be it Color Scales, Data Bars or Icon Sets.

In-cell visuals are nifty, but displaying data and graphics together gets distracting. The fix: Add a new column that references the data column just to its left, so you now have side-by-side columns displaying the same stuff.

Select the new column, click the Conditional Formatting icon in the ribbon, click Data Bars, and click the color scheme you want. Now with those cells selected, click Conditional Formatting again, click Manage Rules and then click the Edit button in the dialog box that appears. In the Edit the Rule Description window, check the Show Bar Only checkbox. Click OK a couple times and now you have a column of numbers and a handy column of graphs right next to it.

slide 13 bad charts
Jim Desmond

Why are my charts so wrong?

Excel makes creating charts so easy, it's dangerous. Keep a few things in mind when you build your charts, and people will hate you less.

1) 3D charts are the worst. Don’t be tempted by amazing-looking 3D chart previews, spinner controls, and shiny colors. 3D charts are almost never more effective, accurate, or understandable than a 2D chart.

2) Charting is like painting a room – do it right and most of your time is spent on cleaning and preparation. Make sure your data is formatted the way you want it to appear in the chart. Be sure the header text in the table is clear and correct. Don’t get tripped up by hidden rows or careless addressing that omits data you need or includes data you don’t. And when deciding on colors, consider a palette that reflects your organization's color scheme and identity.

3) Half the battle here is selecting the right type of chart. Column and bar charts are best for displaying data across categories. Line charts are good for displaying trends over time. Both can support multiple series of data, with variants like stacked column charts. Pie charts only work with a single series and are used to compare parts within a whole.

4) Mind the axes. Make sure labels appearing on the X (horizontal) or Y (vertical) axes don’t take up too much space or get truncated.

5) Clean up in-workbook charts by snapping to gridlines. Click on a floating chart and in the Chart Tools area click the Format menu item. Click the Align down arrow and click Snap to Grid. Now when you drag or resize a chart window, the edge will snap directly to a column or row edge, providing a much neater look for your spreadsheets.

slide 14 bold charts
Jim Desmond

Better bar and column charts

Excel’s default layouts for bar and column charts are anemic. But you can embolden your charts with a bit of custom formatting.

First, click on the chart, then in the Chart Tools menu area click Format. In the drop-down control at the left in the ribbon UI, select Series and click Format Selection just below the drop-down control. In the Format Data Series dialog box, reduce the Gap Width slider from its default setting of 150% down to 75% or even 50%. Now your bars and columns really pop.

You can use that extra space to improve impact. Click the chart and in the Chart Tools area of the menu bar click on Layout, then click the Data Labels icon. In the context menu click Inside End. Chart values now appear inside the bar. Click on one of the labels to select them all. Now click the Home tab in the ribbon and use the font controls to set the font to a larger type – say, 16 point – and color so it stands out from the bar color background.

slide 15 context to charts
Jim Desmond

Add context to charts

You can add valuable context to your charts by adding a line that depicts a target value, average or other metric.

Say you have a column chart that pulls data from a table with months in column A and dollar amounts in column B. To add a line that shows the average amount for all months, in each cell in column C enter a formula that computes the average amount from column B. Then add the column C data as a new series to the chart by clicking the chart, then dragging the data range border so it spans both columns B and C.

Now click on one of the new columns that appear in the chart to select the new data range. Right-click that column and select Change Series Chart Type from the context menu. Click Line in the window on the left and then click the first line graph icon depicted. A horizontal line now runs across the width of the column chart.