# Contextures News 2020-11-17

Excel Custom Hidden Questions

November 17, 2020

Pivot table differences, custom hidden questions, 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.

Thank you for reading the news, and you'll get the next email in two weeks, on December 1st.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

### Show Difference

Most pivot tables just show basic sums and counts for the data. Do you ever use the Show Values As settings, to show the data in a different way?

For example, you can use Difference From and % Difference From in your pivot tables, to show change from the previous year or month, without using formulas.

For more info on the Difference calculations, go to my new page. There are examples, problems to watch for, and layout tips, such as:

• Leave the original values in the pivot table, and show the change in other columns.
• Change the headings, to make the data easy to understand.

### Custom Questions

If you're building a questionnaires in Excel, some questions might have a follow-up item, based on the answer. To keep things simple, use conditional formatting to show the follow-up questions, when needed.

• Cells in rows 4 and 8 have white font and white fill, to hide the questions
• If you select Yes in cell E2, the follow-up question appears.

For details on how to set that up, see my Hidden Questions blog post.

There's a new sample file that shows how those hidden questions work. It has a second question:

Based on the answer, a formula in cell B8 shows the follow-up question for that age group. The options are listed in a table named AgeQs.

• =IFERROR(VLOOKUP(E6,AgeQs,2,TRUE),"")

You can get the Hidden Questions sample file on the Conditional Formatting Examples page, in the Downloads section.

### Excel Articles

Here are a couple of Excel-related articles that you might find useful or interesting.

Charts: Sometimes Excel data is in the wrong layout, when you need to build a chart. As a workaround, Jon Peltier shows how to make a chart data staging area, using formulas, or with Power Query. (Level - Int/Adv)

Data Viz - A few years ago, Chandoo posted an Excel data visualization challenge for financial data. Take a look at the 30 entries, to get inspiration for your own charts and dashboards. If you entered that challenge now, what could you do with Excel's newest functions and features? (Level - Int/Adv)

Also see: My Excel Products || Previous Issues

### Beer and Cheese

We can't go out to pubs or restaurants these days, but last week we went to a virtual beer and cheese tasting. Stonehooker brewery, here in Mississauga, delivered the tasty items a couple of days before the event, then we all logged into a Zoom meeting, for the tasting.

It was more fun and informative than I expected, with the brew master, Adam Cherry, describing his beers, and a cheese expert, Gurth Pretty, talking about the cheeses. We've already signed up for the December event!

And if you've never heard of stonehooking, you're probably not from Southern Ontario!