Contextures

Contextures News 20190702

Top Excel Tips

July 2, 2019

Top Excel tips, file backup macro, 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.

You'll get my next newsletter in 2 weeks -- we're on the summer schedule now.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Top Excel Tips

On Twitter last week, David Napoli asked which top Excel tips we'd teach in a class. There were great suggestions, such as:

  • Use sensible date formats and column names
  • Keep data on a single sheet - not a sheet per month, etc.
  • Use structured data (Excel Tables)
  • Power Query (There was some debate on that!)

Save Your Work: My top Excel tip was to save early and save often! Instead of AutoSave, I use a macro to save a quick copy of the active workbook, in the same folder, with date and time added to the file name.

To get the macro, download my workbook. Then, copy the macro into a workbook that's always open, such as your Personal workbook.

NOTE: If you have my Excel Tools add-in, use the Make Backup command on the CTX Tools toolbar

REPT Function Tricks

In the last newsletter, we looked at the seldom-used T and N functions. Here's another function that doesn't get used too often, but you can do a few cool tricks with -- the REPT function.

With REPT, you can create in-cell bar or dot plot charts, based on the number in another cell. For example, here's how to make a simple dot plot chart:

  1. In cells B3:B5, type 100, 55 and 80
  2. In cell C3, type this formula: =REPT(" ",B3/5-1) & "o"
  3. Copy the formula down to row 5
  4. Adjust the width of column C, so the dot ("o") is at the right edge in cell C3
  5. Then, change any of the numbers in column B, and the related chart will also change

dot plot chart with REPT

You can also combine REPT with VLOOKUP, to find the last text entry in a column. For example, with text items and blank cells in column D, use this formula to find the last text item:

=VLOOKUP(REPT("z",255),D:D,1)

find last text item

See more REPT function examples on my Contextures site, and download the sample file there too.

Excel Articles

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

Data Viz - Google has published their guidelines for great data design. There's an overview on the Fast Company site, and see the guideline details here. (Level - All)

Equations - If you like science as much as you like Excel, check out Doug Jenkins' article on using Excel to solve the Lagranian Point equation for the moon. There's a sample file to download, so you can try it for yourself. (Level - Int/Adv)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

True North

Last month, my husband travelled to Canada's northern coast - the Arctic Ocean. He and a friend drove from Edmonton to Tuktoyaktuk, arriving for the Midnight Sun, on June 21st. They stopped in Whitehorse and Dawson City, but didn't try the Sour Toe cocktail. From there, it was all unpaved roads, and a couple of ferry rides, to Tuk. They enjoyed the adventure, but were glad to get home after that long road trip!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190702ctx.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
dsdalg@ gmail.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: July 1, 2019 2:33 PM