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.

• Show Difference
• Custom Questions
• Excel Articles
• Weekly Photo

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:

• What is your age?

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!

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20201117ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsd @contextures.com
ctxdebra @gmail.com

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.