Contextures

Contextures News 20190917

Excel Worksheet Tips

September 17, 2019

Quick pivot field trick, worksheet navigation tips, 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 helps support the free tutorials on my site.

Worksheet Tips

In a large workbook, you can spend lots of time scrolling through the sheet tabs, looking for a specific sheet. See some of the worksheet navigation tricks that AlexJ uses in his Excel files, to save time. For example,

  • Use tab colours to identify similar sheets, e.g. green for data entry sheets
  • Add "spacer" sheets, to create "white space" between sheet groups
  • Right-click the navigation arrows at the bottom left of Excel, to see a popup list of sheets.

There's a short video that shows the tips, and there are written steps too.

NOTE: There are a few free Sheet Lister tools on my Contextures site too, if you'd like to try those.

worksheet navigation tips

Quick Pivot Field Trick

Instead of dragging pivot fields onto a pivot table, or moving them in the Pivot Table Field List pane, save time with this quick trick.

  • In the screen shot below, I wanted to add the Category field, in the Row area.
  • The Products heading was in cell A4, so I typed "Category" in that cell.
  • When I pressed Enter, the Category field was added in column A, and Product moved to column B.

Go to my Pivot Table Labels page to see a short video that shows the steps.

And if you build lots of pivot tables, save time with my Pivot Power Premium add-in.

pivot table trick

Excel Articles

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

Formulas: Here's an interesting interactive lesson on reading Excel formulas, with helpful tips for understanding how they work (Level - Beginner)

Modelling: To learn about the latest trends, listen to the financial modelling podcast, hosted by Matthew Bernath. There are 39 episodes so far, and the site has blog posts too. (Level - Int/Adv)

Charts: Jon Peltier will host an Advanced Excel Charting Master Class in Chicago, on Mon. Oct 7, 2019. The class is a full-day, hands-on workshop. And if you make lots of charts, check out Jon's time-saving Excel Charting Utilities. (Level - Int/Adv)

Also see: My Excel Products || Previous Issues || Excel Twitter

Late Summer

We're in the final week of summer, here in Canada, and the flowers are still blooming. We strolled through this restaurant garden after lunch last week, and I don't know what these pink flowers are, but the bees certainly like them! It's nice to get away from the computer for a couple of hours, and enjoy the warm weather, while it lasts!.

weekly photo

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

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: September 13, 2019 3:06 PM