One of my favorite new Excel 2007 functions is IFERROR. It provides an elegant way to handle errors that previously would have required ugly IF functions. I use it a lot when I have a column of VLOOKUP functions, which can return #N/A if it can’t find whatever it is looking for. Here’s an example: Continue reading “Using IFERROR function with VLOOKUPs”
I often need to show the last day of the month as a title for columns in a worksheet. It’s nice to use a formula because then, next year, you just change the first month and the rest update auto-magically, accounting for possible leap year (like the one coming up in 2012). Continue reading “Formula for last day of the month using prior month date”
I sometimes get a large report and need to quickly figure out what has changed, if anything. If you have the original and no rows or columns have been added, there is a very fast way to pull out any differences. Lets use the following data as an example. On the left is the original report. On the right is the revised one. Continue reading “Quickly find changed/updated values in a report”
Excel’s Fit to Print scaling feature is quite handy when your print area is wider than what would print out at 100% and you don’t want to monkey around trying to figure out what percentage is optimum. If you want the print area to fit, say, the width of your page and on whatever number of pages it takes to print the rest, here’s handy way to handle that. Just set the Page(s) wide by 1 and delete anything in the Tall field. Like this: Continue reading “Fit to print over variable number of pages”
I often use the ROUND function on percentages. The problem that can arise, however, is when the total is supposed to foot to 100%, but due to rounding it’s a percent off. Continue reading “Avoid percent rounding errors”
By default, Excel aligns a cell’s contents to the bottom. Normally, this poses no problem because typically you only have one line of values or text in a cell. If, however, you use the Wrap Text feature, wrapping text can cause your worksheet to be hard to read. The problem can be severe if you have disproportionate amounts of text between cells in the same row. See the example below. Continue reading “Align to Top when using wrapping text in cells”
I’ve always preferred to put the underline indicating a total or subtotal on the Top border of the cell with the summing function. Many people put it on the bottom of the last row being summed. Normally it doesn’t matter, but if you have to add another line at the bottom, you’ll have to remove and re-add the Bottom border. Continue reading “Use Top border for totals, not Bottom”
One of the truly annoying things introduced in Excel 2007 was its habit to stay running even after all workbooks are closed. I’ve always shut Excel down by clicking the X button in the upper-right corner. The workaround is to press Alt-F4. In theory, this should do the same thing as clicking the X button, but for whatever reason, Excel will exit with the last closed workbook. Enjoy!