Readers of the Lifehacker are already familiar with Denis Batianov, who shared with us the secrets of Excel. Today, Denis will talk about how to avoid the most common problems with Excel, which we often create ourselves.
At once I will make a reservation, that article material is intended for beginning users of Excel. Experienced users have already incendiaryly danced on these rakes more than once, so my task is to save from this young and inexperienced “dancers”.
You do not give headings to the columns of tables
Many Excel tools, for example: sorting, filtering, smart tables, pivot tables, mean that your data contains column headers. Otherwise, you either will not be able to use them at all, or they will not work out correctly. Always take care that your tables contain column headers.
Empty columns and rows inside your tables
This is confusing Excel. When you encounter an empty row or column inside your table, it starts to think that you have 2 tables, and not one. You have to constantly correct it. Also, do not hide unnecessary rows / columns inside the table, rather delete them.
There are several tables on one sheet
If these are not tiny tables that contain value directories, then do not do this.
It will be inconvenient for you to work with more than one table on a sheet. For example, if one table is on the left and the second is on the right, then filtering one table will affect the other. If the tables are located one under the other, then you can not use the fastening of the areas, and one of the tables will have to constantly search and perform unnecessary manipulations to stand on it with a table cursor. Do you want it?
Data of one type is artificially located in different columns
Very often, users who know Excel superficially prefer this format of the table:
It would seem that we have an inoffensive format for the accumulation of information on sales agents and their fines. This layout of the table is well perceived by the person visually, since it is compact. However, believe that it is a real nightmare – to try to extract data from such tables and get intermediate results (aggregate information).
The fact is that this format contains 2 dimensions: in order to find something in the table, you need to determine the line by scanning the branch, group and agent. When you find the right drain, then you will have to find the right column, as there are a lot of them. And this “two-dimensionality” greatly complicates the work with such a table for standard Excel tools – formulas and pivot tables.
If you build a summary table, you will find that it is not possible to easily get data by year or quarter, because the indicators are spread over different fields. You do not have one field by sales volume, which you can conveniently manipulate, but there are 12 separate fields. It is necessary to create by hands separate calculated fields for quarters and year, though, whether it is all in one column, the summary table would do it for you.
If you want to apply standard summation formulas such as SUMIF, SUMIFS, SUMPRODUCT, you will also find that they can not work effectively with this layout of the table.
The recommended format for the table is:
Dissemination of information on different pages of the book “for convenience”
Another common mistake is that, having some standard table format and needing analytics based on this data, spread it on separate sheets of the Excel workbook. For example, they often create separate sheets for each month or year. As a result, the amount of data analysis work is actually multiplied by the number of sheets created. Do not do this. Accumulate information on ONE sheet.
Information in the comments
Often, users add important information that they might need in a comment to the cell. Keep in mind that what is in the comments, you can only look (if you find). It is difficult to pull this into the cell. I recommend that you select a separate column for comments.
A mess with formatting
Definitely will not add your table any good. It looks repulsive to people who use your tables. At best, they will not attach importance to it, at worst they will think that you are not organized and slovenly in business. Strive for the following:
- Each table must have a uniform formatting. Use the formatting of smart tables. To reset the old formatting, use the “Normal” cell style.
- Do not highlight a row or column entirely. Select a specific cell or range for the style. Provide a “legend” for your selection. If you select cells in order to perform some operations with them in the future, then color is not the best solution. Although sorting by color and appeared in Excel 2007, and in 2010 – filtering by color, but the presence of a separate column with a clear value for subsequent filtering / sorting is still preferable. Color – a thing not an unconditional. In the summary table, for example, you do not drag it.
- Make the habit of adding to your tables automatic filters (Ctrl + Shift + L), fixing areas. It is desirable to sort the table. Personally, I was always infuriated when I received every week from the person in charge of the project, a table where there were no filters and fastening areas. Remember that such “trifles” are remembered for a very long time.
Merge cells
Use the union of cells only when without it. Unified cells make it very difficult to manipulate the ranges into which they enter. There are problems when moving cells, when inserting cells, etc.
Combining text and numbers in one cell
A painful impression is produced by a cell containing a number, supplemented from behind by the text constant “RUB.” Or “USD”, entered manually. Especially if it’s not a printed form, but a regular table. Arithmetic operations with such cells are naturally impossible.
Numbers in the form of text in a cell
Avoid storing numeric data in a cell in text format. In time, some of the cells in this column will have a text format, and a part in the usual one. Because of this, there will be problems with the formulas.
If your table will be presented through the LCD projector
Choose the most contrast combinations of color and background. The dark background and light letters look good on the projector. The most terrible impression is produced by red on black and vice versa. This combination is extremely non-contrasting on the projector – avoid it.
Page layout in Excel
This is the same mode in which Excel shows how the sheet will be broken into pages when printed. The page borders are highlighted in blue. I do not recommend constantly working in this mode, which many do, since during the output of data the printer driver participates on the screen, and this, depending on many reasons (for example, the printer is networked and currently unavailable) is fraught with suspensions of the visualization and formula recalculation. Work as usual.
More information about Excel can be found on Denis’s website.
