Mastering Excel takes some experience and patience, but it’s easy to make mistakes even if you’ve been using it for a long time. Sometimes, choices seem like a brilliant idea—until they’re not, and the resulting problems are hard to troubleshoot. In this article, I share 10 ways to avoid actions that seem good… at the time.

I’m using Excel 2016 on a Windows 10 64-bit system. There’s no demonstration file because you won’t need one.

1: Rely on multiple links

Links between two workbooks are common and useful. But multiple links where values in workbook1 depend on values in workbook2, which links to workbook3, and so on, are hard to manage and unstable. Users forget to close files, and sometimes they even move them. If you’re the only person working with those linked workbooks, you might not run into trouble, but if other users are reviewing and modifying them, you’re asking for trouble. If you truly need that much linking, you might consider a new design.

SEE: Build your Excel skills with these 10 power tips (TechRepublic PDF)

2: Destroy data

Users sometimes use the same spreadsheet for new data by overwriting the existing data. For example, you might enter this week’s sales figures over last week’s—destroying last week’s data. In the short run, this process seems innocent enough, but if the data’s important enough to record, it’s probably important enough to keep, at least for a while. Instead of overwriting existing data, use a template to enter new data and name the sheet or file appropriately. Keep your data intact so you can refer to and evaluate it later, beyond its seemingly short shelf life.

3: Rely on default settings

Excel defaults might be adequate for some or even most users, but if you find yourself resetting things every time you start a new workbook file, stop. Instead, open a blank workbook, change those settings, and save the file as a template. Then, base new workbook files on the template instead of Excel’s built-in template. Or alter the default workbook. For more information, read 10 Excel defaults you can customize to work the way YOU want.

4: Ignore Table objects

Excel introduced the Table object in Excel 2007. Tables are efficient, and they’ll make your life a little easier. Table objects come with formatting, formulas, filtering, subtotals, and more built right in. One of my favorites is the always-visible header row, shown in Figure A. Table objects even support data validation; if you add data validation to a cell, the Table extends it as you add new rows. Charts are magically dynamic. Everything’s just simpler with a Table. To learn more about Excel Table objects, read 10 reasons to use Excel’s Table object.

Figure A

Take advantage of the Table object’s many built-in shortcuts, such as always-visible headers.

5: Use Excel as a database or Word processor

I recently received a contract in Excel. When I finally inquired why—I didn’t want to be rude—the client said, “Because no one knows how to use Word.” They need me! It’s tempting to force processes in familiar software, but doing so seriously limits you. While using Excel as an editing tool might not be the worst thing you can do, using Excel as a serious database could get you into serious trouble—the two concepts simply aren’t interchangeable. Use the right tool for the job; if you don’t know how, hire someone who does or consider some affordable training.

SEE: PivotTable Pro Excel Course (TechRepublic Academy)

6: Forget to protect your work

After spending time and expertise developing a great spreadsheet, don’t distribute or share it without applying protection. Failing to do so opens your work to misdeeds—even when users aren’t trying to be malicious. Protect the entire sheet and unlock only those cells that users need to update. Everyone will be glad you did.

7: Leave blanks

Blanks aren’t inherently bad, but they can make built-in features difficult, if not impossible, to use. When Excel encounters a blank, it can fail to sort and filter correctly. Some functions evaluate blanks differently than you might expect. For instance, the AVERAGE() function evaluates the value 0 and an empty cell differently, as shown in Figure B. (The values in both columns are identical except for the blank cell and the 0 in the bottom row.) If a value truly doesn’t exist, consider using a string such as Unknown or NA, or even #N/A, rather than leaving a cell blank. Or resign yourself to features that don’t work as expected—and plan for them.

Figure B

Some functions evaluate blank cells differently than you might expect.

8: Use numbers as column headings

Using a number as a column heading might seem like a good idea—at least until you try to use some of Excel’s built-in features. Numeric headings can wreak havoc on features you take for granted. For example, Figure C shows what happens when using AutoSum. Excel interprets the number 2017 as part of your data range instead of the header row. To the right, 2017 is text, so AutoSum ignores it. Unfortunately, it’s easy to forget such a small behavior. Here’s another reason to use a Table object: Excel won’t evaluate header content.

Figure C

AutoSum evaluates the number 2017.

9: Allow error values

If you share or distribute Excel files with other users, include error-handling functions to avoid error values that others won’t understand and to provide information that they will. Error values aren’t wrong. In fact, they’re great clues to problems, but they’re confusing to end users. They’ll waste their time (and perhaps yours) trying to fix something that’s not really broken.

Excel has several error-handling functions:

• ISNA() evaluates #N/A errors.
• ISERR() evaluates all error types except for #N/A.
• ISERROR() evaluates all errors.
• ERROR.TYPE() returns an integer value that represents the error by type.
• IFERROR() evaluates the error and allows you to return alternate content.

10: Sluff off backups

If you try to exit without saving changes, Excel reminds you. However, Excel won’t remind you to create a backup. This is probably the most egregious decision users make. If you don’t have an automated backup process, consider implementing one. In the meantime, save locally or to an intranet destination as you normally do. Then, create a second copy on a thumb drive or in the cloud. If using a thumb drive, be sure to store it offsite or in a fireproof safe. It sounds like overkill—that is, until you need it.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read…