Contextures

Contextures News 20180220

Excel Budget Reporter

February 20, 2018

Click to change a budget report, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Easy to Read

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.

  • Right-click on one of the items in the Category field
  • Click Field Settings, and click the Layout & Print tab
  • Add a check mark to "Insert blank line after each item label"
  • Click OK, to close the Field Settings.

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.

pivot field with blank line

And if pivot tables are a big part of your job, my Pivot Power Premium add-in can help you save time.

What's in That Cell?

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.

unicode function

Thanks to Mohit Kejriwal for sending the VLOOKUP question, and Alex B for suggesting the CellView add-in.

Budget Reporter

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.

  • Enter all the amounts in a table, and a pivot table shows the summary.
  • Click a report type in the Slicer, and see those values in the pivot table. The Report column in the data entry table calculates which value to show, and macros refresh the pivot table.

There's another Slicer too -- use it to show or hide the zeros on the pivot table sheet.

budget reporter slicer

Excel Articles

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.

Slicers Update: The TrickExcel blog is marked as private now, and no longer accessible - you can see the demo on YouTube, and how to set up the Slicer in this video (no audio).

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)

Also see: My Excel Products || Excel Events || Previous Issues || Excel Humour

Strange Inventions

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.

weekly photo

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: http://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.

Debra Dalgleish
ddalgleish @ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: May 4, 2018 3:28 PM