Millions of people use Excel—arguably one of the most-used workplace applications—every day. Unfortunately not everyone receives formal Excel training, and many people pick it up simply by being forced to use it on the job. If you picked up your Excel skills on the fly, the last thing you want to do is advertise your lack of knowledge.
We’ll show you five typical Excel behaviors that tell the world your Excel skills suck. And because we’re not heartless, we’ll show you how to avoid them.
5 Excel Mistakes to Avoid
1. Skipping Columns
Skipping columns is a classic Excel rookie mistake. It happens when the data in one column flows into the column to the right. Unskilled Excel users start their data in the next empty column—skipping the one that the excess content spilled into. This behavior can cause problems in the future when you’re creating charts and sorting data.
Instead of skipping columns, resize the column with the long entries so that they fit in that column. Here’s how you do it: click the column letter to select the column and, from the Home tab on the ribbon, choose Format > AutoFit Column Width. This widens the column so all the data shows. Another possible solution: Use the Wrap Text tool on the Home tab to wrap the text in the cell so that it fits on multiple lines.
Instead of skipping columns as shown here, smart Excel users learn to adjust column widths.
2. Formulas Don’t Calculate Correctly
The answer to the simple problem 2 + 3 x 4 will tell you if you understand how Excel makes calculations or not. If you think the answer is 14, you’re right. If you think the answer is 20, then you and Excel disagree on some pretty fundamental stuff. If you think I’m crazy, type =2+3*4 into a cell and see.
Excel performs calculations in what is called the order of precedence, and you need to know what this is to successfully write formulas. The order is negation, percent, exponent, multiplication and division, addition and subtraction. You can learn more about Excel’s order of precedence here. You can force calculations to be performed in a different order by placing the parts you want calculated first in brackets, so (2 + 3) x 4, written in Excel as =(2+3)*4 does equal 20.
If you don’t understand Excel’s order of precedence, then your formulas won’t produce the correct results.
3. What does that Chart Show?
People often print Excel charts for reference or copy them into other documents. If you don’t label charts correctly, then you can’t expect anyone to understand them, whether those people are familiar with Excel or not. If your charts can’t stand on their own and inform your viewer, then you’re just making pretty pictures.
Whenever you create a chart, make sure you complete the Chart Title to explain what the chart shows—don’t delete it. Make sure to label the X and Y axes with the type of data plotted on each axis, and include a legend that explains what each color represents. If you want extra kudos, select the chart, from the Chart Tools > Design tab choose Add Chart Element > Data Table, and add a data table to the chart.
Astute Excel users make sure their charts tell a story, and that they’re complete and stand on their own.
4. Constant Data Buried in Cells
When you’re writing a series of formulas that all refer to a single value, such as the tax rate, rookie Excel users put the constant value in the formula. Experienced Excel users place the rate in a cell by itself, and then refer to that cell in their formulas. This makes the worksheet more easily understood and makes checking the accuracy of calculations so much easier.
One reason that beginner Excel users don’t design worksheets correctly is that they don’t understand absolute cell addressing. If you put constant data in a cell and then refer to that cell in a formula, you have to make the cell reference absolute or it won’t copy. If your tax rate is in cell B2, anywhere you refer to cell B2 you must use $B$2 to fix the cell reference so that it doesn’t change. Understanding absolute and relative cell addressing is critical to becoming proficient with Excel.
Placing constant data away from calculations and using absolute cell referencing is a critical Excel skill.
5. Keep It Simple
Too often beginning Excel users learn one function and then use it for everything—even where it’s not needed. SUM is one of these functions. It’s great for adding a range of numbers, but it’s newbie overkill for multiplying two numbers. If, for example, you’re typing =SUM(A1*B1) then try =A1*B1 instead. That’s what an experienced Excel user does.
Bonus Rookie Excel Mistakes
We dug up some other newbie Excel mistakes that you might want to avoid:
- Instead of pressing the Space Bar to delete a cell, click once on the cell and press Delete. Spaces in worksheet calls can cause myriad problems, and they are really hard to trace
- Never use =TODAY()to put today’s date in a cell as a fixed date. TODAY() always shows the current day so, tomorrow it will show tomorrow’s date
- Never use fancy chart types just because they’re available. Column and bar charts meet most needs and nearly everyone understands them. Experienced Excel users use them all the time
- Never use a pie chart for plotting negative values. Excel will make the chart but it’s pure nonsense
- Avoid creating merged cells; they make it impossible to sort data in the future
- Pulling out a calculator, making a calculation and then entering the answer in Excel—a red-flag indication that it’s time to take an Excel class
Avoiding these simple mistakes will improve the quality and efficiency of your work in Excel. When you consider that Excel is used to calculate business finances and track important business indicators you’ll appreciate that good Excel skills are more than for show—they’re often critical to your businesses’ success.
Click this link for even more Excel resources.
Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com
|Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!|