Excel Table Formula Problems
June 2, 2020
You'll get my next newsletter on June 16th -- we're on the summer schedule now, with a newsletter every 2 weeks.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Do you use Names in your Excel workbooks, to identify cells or ranges of cells? Here are a couple of ways to keep track of those names.
To quickly list all the names in the active workbook:
If you want a list with more details, use one of the Excel Name List macros.
And here's a quick way to see range names on the active sheet.
Learn more about Excel names on my Contextures site
If you create Excel formulas that include table references, and then try to copy those formulas to adjacent columns, you might get incorrect results.
For example, a SUMIFS formula gives the correct total quantity in cell C5. However, when you drag that formula across to cell D5, the total is zero, which is incorrect.
The problem is caused by table references that shift when you drag the formula across. The formula in cell C5 referenced 3 columns in the Sales_Data table.
Those references have changed, in cell D5:
To avoid the problem, DON'T DRAG to copy the formula. Instead, use one of these methods:
Go to my Contextures site, to watch a short video that shows the problem of copying formulas in a table, and two ways to avoid that problem.
Here are a few Excel-related articles that you might find useful or interesting.
Security -- The security team at Microsoft reported a recent email scam that sends malicious Excel files, to gain remote access to your computer, The subject line is related to the pandemic, and the file uses Excel 4.0 macros to install the remote access tool. (Level - All)
Formulas: Have you tried Excel's new XLOOKUP function yet? Chandoo has 13 examples that show you how XLOOKUP works. (Level - Int/Adv)
Excel Humour: See what people said about Excel recently. Can you work in a messy Excel file?
When I saw this cookbook in the online library, I had to borrow it -- Procrastibaking: 100 Recipes for Getting Nothing Done in the Most Delicious Way Possible. These are the giant breakfast cookies that I made last week, and they were delicious, but lots of work. You can see the cookie recipe here, if you'd like to do some procrastibaking too!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200602ctx.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.
Last updated: October 7, 2020 4:10 PM