Contextures

Contextures News

Excel dashboard tips

Nov 21, 2017

Dashboard tips, finding links, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Find Links

When you open a workbook, Excel might warn you that it has links to another file. That's okay, if you know what the links are, but sometimes the file isn't supposed to be linked to anything else.

If you run into that problem, download a copy of Bill Manville's free add-in -- Find Links. I've been using it for many years, and it doesn't look fancy, but it does a great job of locating those links for you.

After you install Find Links, it shows up on the Add-ins tab. Click the Find Links button, and a window opens, with a few options. I usually select the option to create a list -- that creates a new workbook with details on all the links. Then, go through that list, and fix or remove the links that you don't want.

There are steps for installing add-ins on my website, and a list of other free add-ins that you might find useful.

mouse wiggle

Excel Names

Thanks to Peter B., who sent me a workbook in which he used UniCode text in his Excel names -- I didn't know that was possible. The basic naming rules are on my Excel names page, and there's a link to a GitHub project which has an in-depth study of what's allowed, beyond the basic letters and numbers.

Do you use Excel names in your files? It's easy to select a group of cells, then type a valid one-word name for those cells, in the Name Box at the left of the Formula Bar. Remember to press Enter, after you type the name. If you forget that step, the name doesn't stick. Later, you can use those names in formulas, or for navigation.

Another easy way to create names is based on text that you're already entered on the worksheet. For example, type "Months", then the month names in the 12 cells below that.

  • First, select the heading cell, and the cells that you want to name.
  • Then, on Excel's Formulas tab, click the Create From Selection command.
  • Check the box to tell Excel where your headings are (top, left, bottom or right), and click OK
  • Excel names the ranges with valid names, based on your headings

troubleshooting with sheet info list

Excel Articles

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

Dashboards - There are 7 tips and tricks for building dashboards, on the Tableau website. You don't need to use Tableau though -- these are great tips for Excel dashboards too. And if you're using charts on your dashboard, take a look at these 5 Data Viz tips from Jorge Camoes. (Level - All)

Excel Fonts - If you want to choose the best fonts for an Excel report, Kevin Lehrbass has a free workbook that lets you compare several fonts at once. And he's right - building something yourself is the best way to learn. (Level - All)

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

Autumn Leaves

The autumn colours look beautiful, until the leaves end up on the ground. There is only one small catalpa tree in our back yard, but it is packed with huge leaves. It gives us lovely shade in the summer, but we pay for that later in the year, with lots of raking.

On the bright side, I did get lots of fresh air and exercise, while raking up those leaves! It's good to get away from the computer occasionally (or so I've heard).

weekly photo

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

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: http://www.contextures.com/newsletter/excelnews2017/20171121ctx.html

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.

 

Search Contextures Sites

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: December 11, 2017 10:26 AM