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.
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:
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.
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.
You can get the Hidden Questions sample file on the Conditional Formatting Examples page, in the Downloads section.
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
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.
Last updated: November 16, 2020 11:23 AM