Contextures News 20191112

Compare Annual Data in Excel

November 12, 2019

Compare annual data, don't destroy a workbook, 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.

Workbook Destruction

Has this ever happened to you? You need to make a new Excel file, so you open an old file that's similar to what you need. Your plan is to make a few changes, and then do a "Save As", to create the new file.

You delete a sheet, and make other changes, and then a co-worker interrupts you for a few minutes. When you get back to your workbook, you accidentally hit "Save", instead of "Save As", and the old file is destroyed! If you're lucky, you have a backup, or IT can retrieve a copy for you, from yesterday's network backup.

To prevent that problem, start with a copy of the old file. If you worked on that file recently:

• Click the File tab on the Excel Ribbon, then click Open, in the list at the left
• In the list of Recent files, right-click on a file, then click Open a Copy

Excel creates an unsaved copy of that file, with a number at the end of its name.

If the file isn't in the Recent list:

• Click the Browse button, and locate the file.
• Click once on the file to select it
• Click the arrow beside the Open button, and click on Open a Copy

There are more Excel Workbook Tips on my Contextures website.

Compare Annual Data

If you have a couple of years of daily data in Excel, you can use a pivot chart to quickly compare that data, month by month, year over year. There are detailed steps and a video on my Contextures blog, but the key step is to group the dates.

If you add a date field to the pivot table layout, Excel might automatically group the dates for you, and show the totals per year (Excel 2016 and later). To see the monthly totals too,

• In the pivot table (not the pivot chart), right-click on one of the years
• Point to the Expand/Collapse command
• Click on the Expand Entire Field command

Then, move the Years field into the Column area, and you'll be able to compare annual data.

NOTE: If Excel is automatically grouping the pivot table dates, you can turn that setting off:

• On the Ribbon, click the File tab, then click Options
• Click the Data category, and at the end of the Data options section, add a check mark to "Disable automatic grouping of Date/Time columns in PivotTables"
• Click OK to apply the new settings

There are more pivot table grouping tips on my Contextures site.

Excel Articles

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

Calendar: On Jon Wittwer's Vertex42 site, you can download an Excel "Random Acts of Kindness" calendar. You can see how all the formulas work, and change the items in the list, or add your own. You could even change it to a "Random Excel skills to learn" list! (Level - All)

Shortcuts: On the Daily Dose of Excel blog, Dick Kusleika shows how to use a keyboard shortcut to open Excel, if you have its icon on your Windows Taskbar. It only works for icons 1 to 9 though. (Level-All)

Reminder: Mynda Treacy shows how to make impressive interactive dashboards, using built-in Excel features, in her one-hour free webinar. Mynda offers full dashboard courses too - get 20% off if you register by Thursday, Nov. 14th. Click here for Excel Dashboard Course or Power BI Course info. Don't miss out - this is your last chance to attend this year (and this decade!)

Excel Tweets: See what people tweeted about Excel this week. How often do you watch Excel go into a death spiral?

Also see: My Excel Products || Previous Issues

Chess Set

A friend came to visit last week, and he brought a hand-carved wooden chess board for our grandson. He bought the chess set in Malawi, in the 1980s, when he lived in Africa for a few years. And with all the snow that we just got, this is a perfect time to stay indoors and play games!

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