10 Things You Should ALWAYS Do (or Not Do) in Excel
It was a fairly innocuous vent-via-Twitter tweet last week that inspired this post:
I was surprised by the Twitter conversation it started, with several people noting that they had no idea you could simple turn off the gridlines. There was only one vigorous defender of the all-white fill: Michele Kiss pointed out that, for both Windows and Mac, if you launch a New Window for your spreadsheet (so that you have two or more windows to allow viewing multiple worksheets in the same workbook at the same time), that the gridlines return. She’s right. And, no amount of changing of Excel’s settings or saving default book.xlt and sheet.xlt files seems to get around that. It would be pretty easy to put a macro in your personal workbook to toggle gridlines from a hotkey…but I don’t use New Windows all that often, so I didn’t pursue it.
That’s all somewhat beside the point. During the ensuing exchanges from that initial tweet, Alyson Murphy nudged me to see if I could make a quick blog post of other little things like this in Excel. I jotted down a list and had ten things before I knew it, so here goes! They’re numbered…but they’re in no particular order.
No. 1 – Turn Off Gridlines
The tip/opinion that spawned the post. I won’t belabor it. Just know that there’s a checkbox under the View >> Show group in Windows and under Layout >> View on the Mac. Deselect it, and the gridlines go away.
No. 2 – Vertical Align: Top
Definitely on the top 10 list of asinine Excel default settings is the bottom-alignment for cells. It looks unnatural. It’s not how the human brain wants to read a multi-line row. Once text starts wrapping in a cell and rows go to multi-line heights, you’re going to want the single-line contents to be top-aligned. Sometimes, you may want it middle-aligned, but never-never-never bottom-aligned. That’s just silly.
No. 3 – Default Workbooks, Worksheets, and Charts
Excel has so many atrocious defaults that, if you start from the defaults with every new workbook, you could easily spend 2-10 minutes just undoing their ickiness. That should only be necessary when you’re passed some sort of default-heavy abomination from someone else. For your new workbooks, you can save a default workbook and a default worksheet into the xlstart folder. Turn off gridlines, vertical align all the cells, cut the number of worksheets from 3 to 1, even change the font from Calibri to something else. Do what you want and save it as a workbook and worksheet template.
You can also save charts as templates: turn off drop shadow, switch to non-default colors, remove tick marks, lighten up gridlines, adjust font sizes, etc. Then…save it for future use!
I’d love to say I also regularly customize themes so that I have different palettes to choose from that I’ve truly customized, rather than manually setting chart and table colors as I go. I occasionally do that, but I always have to go re-remind myself how. But, if you’re bothered by the default Office theme, and none of the other ones that are pre-created suit your fancy, you can totally make a palette that matches your company’s color scheme. If you do that, take the time to track down the actual RGB colors — don’t just eyeball them. There are a half-dozen different easy ways to do that…but that’s a topic for another post.
No. 4 – Building Error Checking into Your Formulas
There is no need for your workbooks to ever show #DIV/0, #N/A, #REF, #NAME?, #NUM!, or #NULL!. If you’re distributing a workbook to other users, these are jarring values to see, and they can cause a momentary hesitation as to the veracity of the entire workbook.
I have a standard formula structure that I use any time I’m building a workbook that will be distributed for any cell that, over time, may result in one of the above errors. These errors can be totally legitimate…but I want them handled more elegantly. For instance, say I have a workbook that shows revenue by search keyword and also compares that revenue to the revenue for the same keyword the previous month. Each month, I’ll import new data, and that list of keywords will be updated. So, what happens when I have a keyword that generates NO revenue one month? The next month, when I try to calculate the % change in revenue, I’m going to get a #DIV/0 error:
With a slightly more complicated formula (but, trust me, you get used to this structure really quickly and learn how to copy and paste even more elaborate base formulas into the structure), I can permanently eliminate that error display.
Essentially, here’s the logic I build into the formulas:
- Evaluate the formula
- If it returns an error, then put something clean in the cell rather than an Excel error
- If it does not return an error, then put the result of the formula in the cell
[The next paragraph and image were updated based on Alyson Murphy’s note in the comments. I was aware of IFERROR, but I’d always misinterpreted how it worked. I’ve now replaced what was originally in the following paragraphy — IF(ISERROR(<formula>,”-“,<formula>) — with the simplified IFERROR() function shown below.]
I (now) use the IFERROR() function for this, which is the broadest error-checking function in Excel (if you’re curious, you can read up on ISERR() and ISNA()). So, rather than the formula shown above, I put the formula inside an IFERROR() function:
The error result doesn’t have to be “-“. It can be null (“”) or 0 or even a cleaner error message (“N/A”, “Unknown”).
This isn’t just for aesthetics. It can also be used when you’re running formulas on a column of evaluated values. Depending on the formula, a single error value may cause the aggregating formula to error out as well!
I use this structure all the time!
No. 5 – Print Preview Is Your Friend
95% of the time, your workbooks are viewed online. And, sometimes, there are so many columns that printing isn’t even feasible. But, it drives me nuts when I get a workbook that should be printable…but the analyst clearly hasn’t spent two minutes making that easy to do cleanly.
Use print preview to check printability. I almost always reduce the page margins to 0.5″ to help out. But, I also, rather than simply clicking the “fit to page” checkbox, do a little massaging of column widths in the base document so that the aspect ratio of the viewable content is printer-friendly.
And, of course, don’t forget to:
- Select rows to repeat on every page if the document has column headings that should appear on every page
- Add a footer with a page number and other useful information
There’s something of a minor art to support printability, but, if someone is trying to print out the workbook, it’s worth giving them a file that supports that!
No. 6 – Absolute and Relative Cell References
This may be a no-brainer, but I’m regularly surprised when I see formulas that don’t appropriately use “$” in the cell references to support dragging formulas down rows and over columns.
No. 7 – Named Cells and Named Ranges
I might have an unhealthy adoration of named cells and ranges. But, related to the previous tip, if I’ve got a cell or a range of cells that I know I’m going to be keying off throughout the workbook (i.e., the report date), I make it a named cell. All it takes is selecting the cell and then clicking in the box at the top left of the workbook and giving it an intuitive name. Then, I can use that name rather than a cell reference anywhere in the workbook.
Named ranges can be a huge timesaver when it comes to charting — let them do the heavy lifting of adjusting the timeframe to display. Hands-down my most popular blog post ever was this one on using dynamic named ranges for charting.
No. 8 – Excel Tables — A Special Kind of Named Range
I also have something of an obsession with Excel tables. But, that’s only because they’re so freakin’ awesome! I’ve written an entire blog post on that front.
No. 9 – Dropdown Selectors…Using Tables
I always use tables in conjunction with data validation to make in-cell dropdown selectors. It requires using the INDIRECT() function, which is an inexplicable, but minor, Excel quirk. Details are in the same blog post I referenced in the prior tip. Just scroll down to the “Referencing Tables and Parts of Tables” section.
No. 10 – Tricks within a Cell
I’m doubling up on this one, because they’re both related to entering stuff inside of cells:
- To force Excel to display the contents of a cell exactly as you entered it — not converting something that looks like a date to a date, not removing leading zeros (although this can be done with a custom text format as well), or something else — precede the contents of the cell with an apostrophe. Annie Cushing wrote a recent post where she lays out how to use the apostrophe to “save” a complex formula mid-stream (since Excel won’t let you enter a “broken” formula).
- Line breaks are doable within a cell. Sometimes, you want to make a mini-list inside a single cell, for instance. Other times, you want to put two paragraphs. In Windows, simply press <Alt>-<Enter> for a line break. On the Mac, press <Alt>-<Cmd>-<Enter>
And, of course, custom cell formats are super-super handy (Do you want a “+” displayed in front of positive numbers AND a “-” displayed in front of negative numbers? There are cases where you do, and custom formats are your friend!). Jon Peltier wrote a great post explaining the ins and outs of custom formats, and I regularly find myself returning to that post for a refresher.
And That’s It! Except…it’s not…
This post hasn’t included, I realized as I wrote it, some of my other favorites: pivot tables and the GETPIVOTDATA() function, the TEXT() function (when concatenating strings to have a cell say something like, “Visits increased by 20,135 (8% growth) over the prior week” or “Report Dates: January 1, 2014 to January 8, 2014”); conditional formatting for in-cell bar charts for quick and condensed visualization of a list of numbers; the xlVeryHidden worksheet property; worksheet and cell protection; the triple thread of INDEX(), MATCH(), and OFFSET(); and on and on…
But, I have to stop somewhere!
What are your favorite tips / underused Excel capabilities?
