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.
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!
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:
Do you ever use those Name Box tricks? There are more Data Entry Tips on my website.
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.
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
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!
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 @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: March 15, 2017 11:57 AM