Contextures News 20200505

Excel Pivot Table Shortcuts

May 5, 2020

Group colour banding, pivot table shortcut, 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.

• Colour Banding
• Pivot Shortcut
• On Contextures
• 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.

Colour Banding

When you create a named table in Excel, you can colour the alternating rows with one of the built-in Table Styles. But how could you colour alternating groups of information, such as dates? Here's a colour banding technique that I adapted from Chip Pearson's site.

In this example, column D, has the heading TRUE, and this formula, which returns TRUE or FALSE.

=IF(A1=A2,D1,NOT(D1))

Then, conditional formatting was applied to the table:

• Select all the data cells in the table
• On the Home tab, click Conditional Formatting, New Rule
• Click on "Use a formula to determine which cells to format"
• In the formula box, type this formula, referring to the active data cell: =$D2=TRUE
• Click the Format button, and choose a fill colour
• Click OK, twice, to apply the formatting
• (Optional) Hide the TRUE/FALSE column, to tidy up the worksheet.

Another way to separate the groups is with a top border, like I did with this list of dates.

There are more Conditional Formatting examples on my Contextures website.

Pivot Shortcut

There are little plus and minus signs in a pivot table, that let you show or hide the details for a specific item, such as the Bars category (shown below), or an entire field, like Category.

Did you know that you can use the mouse scroll wheel to expand and collapse too?

In a pivot table, point to a cell in the Row or Column area.

• To expand, press Shift and scroll UP with the mouse wheel
• To collapse, press Shift and scroll DOWN with the mouse wheel

You'll get different results, based on the cell you're pointing at, so experiment to see how it works.

There are more Pivot Table Collapse/Expand tips on my Contextures website.

On Contextures

Formula Challenge: See my blog post about last month's formula challenge on odd/even week totals. I explained how some of the solutions work, and how we can avoid hard-coded values in the formulas.

Excel Articles

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

Excel Tips: Work faster in Excel, using the tips, shortcuts and new features that Wyn Hopkins will share next Wednesday, May 13th (Level-All)

Spreadsheets: On Reddit, someone asked, "What's the most impressive/most useful spreadsheet you've created for work?", and there are some interesting replies. What's yours? As always, read Reddit at your own discretion! (Level - All)

Excel Skills: Take a look at the free 4-week online Excel course, from the University of Colorado - Everyday Excel Part 2. It covers advanced data management, Excel for financial applications, and more. (Level - Int/Adv)

Excel Humour: See what people said about Excel recently. Are your Excel files like escape rooms?

Also see: My Excel Products || Previous Issues

More Flowers

We had beautiful weather on Sunday, so I spent time weeding the garden, and cleaning it up. Now the weather forecast says we'll have below average temperatures for the next couple of weeks, and probably a snowfall or two. Sigh. That's why do don't do any planting until the Victoria Day weekend.

This plant, a hyacinth I think, is a bit straggly, but the bright pink flowers look nice in a close-up shot. I hope it survives the cold days ahead!

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200505ctx.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

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.