Excel Budget Reporter
February 20, 2018
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
To make a complex pivot table easier to read, add a blank line after each item in the main row fields. In the screen shot below, the outer field is "Category", and I'd like a blank row after the Revenue Total.
Don't do this for every field, or the pivot table will be too spread out -- use this setting on one or two of the outer fields. See more pivot table field settings on my website.
Last week, you saw a VLOOKUP error mystery, and could download the sample file, to look for the problem. There were hidden characters in the lookup table codes, and I wrote about the troubleshooting steps on my blog. I've updated the sample file, with a few more solutions.
Because the characters were part of the standard character set, the CODE function didn't identify them correctly. In a comment on the blog post, XLarium said to use the UNICODE function. It works with non-standard characters, and is available in Excel 2013 and later.
Another way to see what's in a cell is with Chip Pearson's CellView add-in. It's free to use (shareware), but doesn't show the correct codes for non-standard characters.
Thanks to Mohit Kejriwal for sending the VLOOKUP question, and Alex B for suggesting the CellView add-in.
There's a budget tutorial on my website, and it shows how to set up a workbook with forecast and actual budget amounts, and calculate the variance.
I've added a new sample file (#2 in the download section) - it shows a different approach for entering and reporting the budget amounts.
There's another Slicer too -- use it to show or hide the zeros on the pivot table sheet.
Here are a couple of Excel articles that you might find useful or interesting.
Slicers - On the TrickExcel blog, Stefan shows how to create fancy single selection Slicers, with filled or empty circles, to look like radio buttons. (Level - Intermediate) Thanks to UniMord.
Power Query - Learn more about Power Query, from experts Ken Puls and Miguel Escobar. Get the free trial of their online Power Query Academy, and watch 7 videos from their full course (about 1 hour). (Level - Intermediate/Advanced)
Do you have a basement or attic that is full of "treasures"? Clearing out our basement is a neverending task, but it's fun to find some of the strange things that we've saved. I don't think we ever played this game, but it's entertaining to see the old patent drawings, and guess what the inventions are.
Can you figure out what this one is? I've put the answer just above my signature, so scroll down when you're ready to see it.
That's it for this week! If you have any comments or questions, send me an email.
NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180220ctx.html
I'll also post any article updates or corrections there.
ANSWER: The invention is a Bicycle Mitten - a hand warmer and protector, that slides over the handlebar. It has a thumb hole so you can ring the bell, when necessary.
ddalgleish @ contextures.com
Last updated: May 4, 2018 3:28 PM