Contextures News 20200602

Excel Table Formula Problems

June 2, 2020

Stop table formula problems, see range names, 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.

You'll get my next newsletter on June 16th -- we're on the summer schedule now, with a newsletter every 2 weeks.

• Excel Names
• Table Problem
• Excel Articles
• Weekly Photo

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Excel Names

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.

List the Names

To quickly list all the names in the active workbook:

• Go to a blank sheet, and press F3 to open the Paste Name box
• Then click the Paste List button, and the list is added to the worksheet.
• It lists workbook scope names, and worksheet scope names for the active sheet

If you want a list with more details, use one of the Excel Name List macros.

See the Names

And here's a quick way to see range names on the active sheet.

• Reduce the worksheet zoom to 39% or lower
• The range names appear, in blue text

Learn more about Excel names on my Contextures site

Table Problem

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.

• =SUMIFS(Sales_Data[Quantity], Sales_Data[Region],$B5, Sales_Data[Category],C$4)

Those references have changed, in cell D5:

• =SUMIFS(Sales_Data[TotalCost], Sales_Data[Category],$B5, Sales_Data[Product],D$4)

To avoid the problem, DON'T DRAG to copy the formula. Instead, use one of these methods:

• Fill Right - Select the cell with the formula, and cells to the right, then fill right (Ctlr+R)
• Copy and Paste - copy the cell with the formula (Ctrl+C), then select one or more cells, and paste (Ctrl+V)

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.

Excel Articles

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?

Also see: My Excel Products || Previous Issues

Procrastibaking

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.

Debra Dalgleish
ddalgleish @contextures.com

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.