Excel is one of the most popular programs in the business world today. It is used in many applications, including data analysis, accounting, finance, inventory control, and project management.
With so many different ways to use this program, it can sometimes be tough to know how to get started with some of the more advanced features.
To help you avoid some of the common errors and mistakes that people make when using excel spreadsheets, here are 6 of the most common errors and how to deal with them.
1) Cell References That Aren’t Working
Okay, so you’ve got your fancy formula all setup and running—but when you copy it into a new cell, it won’t do anything. That’s because, when writing Excel formulas and referencing other cells, you must use cell references that will work once you’ve copied and pasted your formula. There are three ways that you can make sure your cell references are going to reference something valid even after copying: relative references, absolute references, and mixed references.
Understanding which of these approaches is best in different situations is important for ensuring that your data remains accurate. So what does each approach mean?
2) Duplicate Column
Names in Different Workbooks
The definition of a duplicate can be a little fuzzy in Excel. If you have two sheets or workbooks that both contain a column named A, then one of these columns can refer to a different cell in each workbook.
The solution is simple: Give unique names to your columns. And if you change any of your worksheets, make sure they all have unique column names so there are no duplicates within your workbook. Otherwise, you’ll get an error when you try using VLOOKUP and other functions on certain cells.
3) Too Many Excel Objects on the Sheet
If you have too many objects on your sheet—including charts, shapes, text boxes, data validation drop-down lists, conditional formatting, and so on—your computer may run out of memory. Your computer will try to keep working regardless of what’s happening in your spreadsheet, which can make it appear like nothing is wrong.
So even if you can’t see or interact with an object on your sheet because there are too many objects already, you might still want to use caution when adding new objects. If Excel becomes unresponsive while working on a spreadsheet that has lots of existing objects—objects that were there when you started typing but are no longer visible—press CTRL+ALT+DELETE and then click End Task. This will cause Excel to quit immediately.
4) Formula Results Not Displayed Correctly
Sometimes you need to perform calculations on text, but that can cause all sorts of issues in Excel. To avoid bad numbers and get your spreadsheet back on track, make sure that your data is in a format that Excel can read as text. If you’re working with date values or codes, for example, chances are good that they’re being treated like numbers instead of dates or characters.
To change a value from number to text: Select the entire column—click anywhere within it and hold down Shift while pressing End; click on Data within Excel’s Home tab, and then choose Text to Columns. Change Delimited under Step 1 into an option like Commas or Tab.
5) Text Data Displayed as Numbers
Sometimes Excel data that is supposed to be text is displayed as numbers. For example, you might enter a date as of 6/26/10 and it comes out as 06/26/10 instead of displaying it as a date. In these cases, you just need to tell Excel that what you’re entering are dates by changing their format in Excel.
Conclusion:
Identifying and fixing errors is not a simple black-and-white process. But when you do fix errors, you get some serious learning out of it. If you want to learn more about Excel, you can always learn for free on youtube. There are thousands of Excel tutorials on YouTube. Use Career Ninja‘s LearnTube platform for hand-holding training on YouTube. Learn Tube organizes the results of your YouTube search into a course framework. If you want to learn “Excel tutorials”, search the term on LearnTube and it will show you a bunch of youtube videos like an online course. As a beginner, you’ll click through the videos from the first to the last, as if you were taking an online course tailored specifically for you.
