Excel: Most Common Mistakes To Avoid

Five mistakes that are often made when using Excel spreadsheets. What are they, and how can they be fixed? Many of our readers work with Excel every day. We have often talked about the most valuable functions and those features that help save precious time by allowing you to finish the job effectively and without making mistakes.

One of the most amazing tools to extract data and format it in Excel is Flash Fill, but in the folds of the Microsoft software, there are many exciting features, some of which, if not used correctly, can cause some difficulties. In this article, we focus on five common mistakes that can be made when working with an Excel spreadsheet.

Five Prevalent Mistakes When Using Excel spreadsheets

Use A Single Sheet To Manage Data, Create Reports And Generate Graphs

It is common to come across Excel files that contain data, information and charts all condensed into a single sheet. On the one hand, the data being processed, the various calculations and formulas are entered in another area, and graphs and reports are in another. To avoid confusion, it is essential to use multiple sheets in the same Excel file: click on New sheet at the bottom of the interface, then rename each sheet to separate, for example, calculations, data and graphs.

You can explicitly reference other sheet names in formulas to update calculations and charts as you change the data. By clicking on the Card tab color, it is possible to change the color assigned to the cards corresponding to each spreadsheet to make them more easily recognizable. Tabs can also be grouped for convenience.

Insert Numbers Into Formulas That Do Not Depend On The Value Of Other Cells

One of the most common errors encountered when working on Excel sheets is the presence of hard-coded numbers in formulas. If a formula is based on data that does not change (think, for example, of the 24 hours that make up a day), the number can be used, but it is a mistake to use data that can be subject to change in formulas.

For example, think of a percentage value: it is never a good idea to insert it in a formula because it could change, and if it were used in various recipes, it would not be easy to identify and modify all of them. By selecting the cell containing a value used in the formulas (for example, a percentage), then clicking on the Formulas menu and finally on the Find dependent cells button, Excel shows the cells whose value depends on the one shown in the partition on which you clicked.

Using the Find Dependent Cells button, you can establish relationships between cells and understand why a spreadsheet doesn’t calculate values ​​as well as you would expect.

The Find Previous Cells button is also helpful because it lets you determine which cells are referenced by a formula in another cell.

The relationships are highlighted with blue arrows, which, once the necessary checks have been carried out, can be removed by clicking on Remove arrows. Suppose you want to correct a number entered ( hardcoded ) in the formula by instructing Excel to consider the one reported elsewhere for the calculations, highlight the reference in the procedure, and then click on the cell containing the number to use.

By pressing the F4 critical, Excel corrects the references to the cell within the formula: in particular, the spreadsheet applies the absolute references for the indicated cell. By clicking on the small square in the lower right corner of the cell, the formula thus corrected can be automatically extended to the other neighboring cells, horizontally or vertically.

Don’t Use Tables In Excel

Another widespread mistake when using Excel is not using tables. If formulas, charts, pivot tables and conditional formatting need to update automatically when, for example, new rows are added, Excel tables are a must.

To transform a set of Excel data into a table, click on the Home menu and then the Format as table button. The spreadsheet allows you to select a layout for the table and, in any case, to customize its formatting. It is also asked whether the first row should be treated as containing column headers.

Once the table has been created, it is possible to add or remove entries: the elements that depend on the data set will update accordingly. You can understand where an Excel table ends by examining the marker that we have highlighted in the figure with a red arrow.

Merge Cells, Then Create Formulas

Selecting multiple cells in Excel and then clicking Home, Merge, and Center Align can cause a string to be placed in the center of the merged set of cells. Usually, Excel cells are merged to create “headings”, under which various columns are developed with lots of different data.

The problem is that when you create a formula that uses part of the data in the column, the presence of cells combined does not allow you to make the formula itself. To fix it, undo the merge operation of the cells (click on the Merge and align center button ), then select the cells again, right-click on them and choose Format Cells. Finally, on the Alignment tab, select the Align center in columns.

With this straightforward trick, they will no longer have problems managing the various formulas.

To find all the cells that have been merged in the Excel spreadsheet, click Home, the Find and Select button, Replace and finally, Options. With a click on Format, Format and checking only the Merge cells box, you can search for all the cells merged in the sheet you are working on.

Use External Links

Look at what appears by clicking on the Data menu of the Excel spreadsheet: the Edit Links button should generally be grayed out or not selectable. If, on the other hand, by clicking on Edit links, you find references to other files, it’s a good idea to do some cleaning. This happens in those companies where it is customary to copy data from other Excel files into the new spreadsheets.

To remove all references to other Excel files once the sheets of interest have been copied, click on Edit links on the Change source button, then specify the Excel file you are working on. This will eliminate links to other files and only use references to sheets within the Excel file you are working on.

Also Read: The Best Antivirus Apps For Your Phone