Contextures

Contextures News 20180410

Excel Embarrassment

April 10, 2018

Add (almost) hidden properties, 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 supports the free info on my site!

How Embarrassing!

Last week, I shared a few double-click tricks, to help you save time in Excel. One place where a double-click doesn't work is at the bottom of the formula bar, after you've dragged it down to make it bigger. That trick worked in Excel 2007/2010, but was dropped in 2013.

This week, I finally noticed the little arrow at the right end of the Formula Bar. Click that arrow to toggle between the original size, and the expanded size. I'm embarrassed that it took me 11 years to find it! The double-click was handier though - it worked anywhere along that bottom border.

Are there any Excel features that you finally noticed, years after they were added?

Formula Bar arrow

Excel Properties

If you create Excel files for clients or co-workers, this tip from Alex B might help you. His co-workers sometimes need help with a file that he created, but they've changed the file name, etc., so it's hard to track down the original file.

To make it easy to locate the original, Alex puts the original file name and path in the workbook's Comments property, where most people never look. He uses a macro to add that "hidden" property, but here's an easy way to do it manually.

First, add a couple of commands to the Excel Ribbon:

  • Right-click on the Excel Ribbon, and click Customize the Ribbon
  • In the list on the right, add a new group on one of the tabs
  • At the top left, choose Commands Not in the Ribbon
  • Find the Document Location command, and add it to your new group
  • Then, choose All Commands, find "View Document Properties", and add it
  • Click OK to close the Options window

Then, use those commands:

  • Click in the Document Location box, and press Ctrl+C to copy the path
  • Click the View Document Properties command, then click in the Comments box
  • Press Ctrl+V to paste the path in the Comments property.

If you'd like the steps for creating a new group on the Ribbon, see my Scenario example. Then, after you test your Ribbon changes, remember to go back in, and export your customizations.

View Document Properties and Document Location

Excel Articles

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

Charts - Jon Peltier shows how to make Gantt charts in Excel -- they're bar charts used for schedules and project management. And if you do lots of work with charts, check out Jon 's Chart Add-in. (Level - Int)

Excel Help - The Stack Overflow site is a great place to get help with your Excel macros. It's their 10th anniversary, and Joel Spolsky tells how the site got started. He's also worked for Microsoft and designed Excel Basic.

Macros - Reminder - Jon Acampora is hosting free webinars this week - "7 Steps to Getting Started with Macros & VBA". Jon explains how you benefit from learning VBA, and how to get started. There are 2 webinars each day, from Mon, April 9th to Fri, April 13th. Click here to pick a date and time that works for you. If you want to go beyond the basics, Jon also has a full macro course. (Level - Intermediate)

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

Spring Weather

We had some wild weather last week, with strong winds that knocked over our patio table, and blew away the heavy mat from our front porch. Then we got snow - enough that it stayed on the ground for a while. Still, it wasn't as bad as the blizzard in early April 1975, when it took me 7 hours to drive home, instead of the usual 30 minutes. I can't find Toronto pictures, but nearby Barrie was buried in snow.

weekly photo

That's it for this week! If you have any comments or questions, send me an email.

NOTE: For the online version, paste this URL into your web browser: http://www.contextures.com/newsletter/excelnews2018/20180410ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
ddalgleish @ 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: April 7, 2018 2:37 PM