Contextures News

Excel Name Box Tricks

February 28, 2017

Name Box tricks, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Calculated Fields

Pivot tables are a great way to summarize a large amount of data, without fancy formulas. But if you need to, you can create formulas within a pivot table, with calculated fields and calculated items.

For example, a calculated field can check the total number of units sold for each product, and apply a bonus if more than 100 were sold: =IF(Units>100,Total*3%,0)

You can edit those formulas too, so I've made a new video that shows how to change a calculated field, after you created one.

And if you use pivot tables, get my free pivot table add-in too!

Name Box Tricks

There is a Name Box, to the left of the Formula Bar, and you can use it to quickly name a range of cells. After that, you can click the arrow in the Name Box too, to select a named range.

Another handy trick is that you can type cell references in the Name Box, then press Enter to select those cells. For example:

  • If columns A, E and H are hidden, type A1,E1,H1 in the Name Box, and press Enter. Then use the Unhide command to show those columns
  • Or, type A1:A1000 in the Name Box, and press Enter to select those cells. Then enter values or a formula in all the selected cells.

Do you ever use those Name Box tricks? There are more Data Entry Tips on my website.

custom tab on ribbon

Excel Articles

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

Excel Basics - If you know someone who needs help getting started with Excel, download a free copy of Simply Learning Excel. It has screen shots from 2007, but works with newer versions too. (Level - Intro)

Excel Tools - 1) The Frankens Team have a free Excel Formula Analyzer add-in, to help you troubleshoot your complex formulas. 2) If you downloaded Andrew Engwirda's free VBE Tools a couple of weeks ago, he has released an update. (Level - Intermediate/Advanced)

NOTE: If you download and install Excel add-ins, be sure to unblock them, or they won't appear in Excel.

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

Course Closing

Power BI: Registration ends this week for Mynda Treacy's Power BI Course, so sign up now if you want to attend. The doors close on Thursday March 2nd, 8PM Pacific

Power BI Webinar

Young Woman in Tech

Did you identify all of office gadgets in last week's photo? The answers are below this week's picture, and you can see the details on my Debra D Blog.

This is Women in Tech Week here in Canada, and it reminded me of this photo of my granddaughter. She was about 18 months old, and "helping" me do some work. I didn't pose her with that Excel Add-in Development book -- she picked it herself, out of all the books in my office. Smart girl!

weekly photo

Last week's gadgets: 1-CD Labeller; 2-Coffee Cup Unroller; 3-Solar Charger; 4-Business Card Case; 5-Scissor Sharpener; 6-Bottle Opener/Flash Drive; 7-Calculator; 8-Pen

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

ddalgleish @

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.




About Debra






Last updated: March 15, 2017 11:57 AM