Best way to use a pivot table as data for a report

If you know how to use Pivot tables, you may be tempted to use one as the source data for a report. You should almost never do that. Pivot tables are not meant to be static . If the data that drives the pivot table changes unexpectedly, your report changes and you may not realize it. Not only that, but when you reference a cell in a Pivot table, you get the weird¬†GETPIVOTDATA function which doesn’t copy relatively very well. Continue reading “Best way to use a pivot table as data for a report”

Don’t hide rows/columns, use Group feature

Take a look at this worksheet snippet. What do you see?

Right off the bat, you should notice is that rows 11 to 15 are grouped and hidden from view. A closer look should also tell you that rows 3 to 8 are hidden. Continue reading “Don’t hide rows/columns, use Group feature”

Using VLOOKUP function to detect new lines of data

Let’s say you have a Balance Sheet that you update each month with data from your accounting system. The thing that always trips you up is when a new GL account has been added and it isn’t already in your Balance Sheet. If you’ve got a lot of accounts, this can be a daunting task to do manually. Continue reading “Using VLOOKUP function to detect new lines of data”

Using IFERROR function with VLOOKUPs

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”

Formula for last day of the month using prior month date

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”

Quickly find changed/updated values in a report

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”

Fit to print over variable number of pages

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”

Align to Top when using wrapping text in cells

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”