12 of the most common problems with Excel and how to solve them
Tips Technologies / / December 19, 2019
Layfhakera readers are already familiar with Denis BatyanovymWho shared with us Excel secrets. Today, Denis will talk about how to avoid the most common problems with Excel, we often create themselves independently.
Immediately I say, that the material article is intended for novice users of Excel. Power users already incendiary danced in these rake more than once, so my task is to protect it from this young and inexperienced "dancers".
You do not give table headings
Many Excel tools, such as sorting, filtering, smart tables, pivot tables, - mean that your data contains column headings. Otherwise, you will either not be able to use them, or they will work is not entirely correct. Always take care to your tables contain the column headers.
Empty columns and rows in your tables
It knocks Excel avail. Faced with an empty row or column in your table, he begins to think that you have 2 tables instead of one. You have to constantly correct him. Also, do not hide your unwanted rows / columns within a table, it is better to remove them.
On one sheet is more tables
If this is not the tiny tables containing reference values, so that you should not do.
You will be inconvenient to fully work with more than one table per sheet. For example, if a table is placed to the left and the second on the right, then filtering the same table will affect the other. If the tables are arranged one above the other, it is impossible to use fastening areas, as well as one the tables have to constantly search for and produce extra manipulation to get up on her tabular cursor. It is necessary to you?
These artificially one type are arranged in different columns
Very often people who know Excel is sufficient surfactant to prefer this format of the table:
It would seem to us innocent format for storing information, sales agents and their penalties. Such a table layout is well perceived by the human eye, as it is compact. But, believe me, it's a nightmare - trying to retrieve data from such tables and receive interim results (aggregate information).
The fact that this format contains two dimensions: to find something in the tableYou have to decide on the line, touching a branch, group and agent. When you find the right drain, then later have to look for the column already, as their lot here. And this "two-dimensionality" greatly complicates the work of such a table for the standard Excel tools - formulas and pivot tables.
If you build a pivot table, you will find that you can not easily get the data of the year or quarter, as the figures are carried on different fields. You do not have a field for the volume of sales, which can be conveniently manipulated and there are 12 individual fields. We'll have to create a separate calculated field hands for the quarter and the year, though, whether it is all in one column, the summary table would have done it for you.
If you want to apply a standard formula of summation type SUMIF (SUMIF), SUMIFS (SUMIFS), SUMPRODUCT (SUMPRODUCT), it will also find that they are able to work effectively with this arrangement table.
Recommended format of the table is as follows:
Diversity of information on the different sheets of the book "for convenience"
Another common mistake - it's having some kind of standard format of the table and in need of intelligence based on these data, distribute it on separate sheets of an Excel workbook. For example, often create separate lists for each month or year. As a result, the amount of work by actually analyzing the data multiplied by the number of sheets created. It is not necessary to do so. Collect information on a single sheet.
The information in the comments
users often add important information that they may need, in a comment to a cell. Keep in mind that there is in the comments, you can only view (if you can find). Get it into the cell difficult. I recommend better allocate separate column for comments.
Mess with formatting
Definitely not add to your table any good. It looks disgusting for people who enjoy your tables. In the best case, this does not give the value, at worst - will think you are not organized and sloppy in business. Strive for the following:
- Each table should have a uniform format. use formatting smart tables. To clear the old format using cell style "Normal".
- Do not select the color of the whole row or column. Highlight the style of a particular cell or range. Provide a "legend" of your discharge. If you select a cell to further make them any operation, the 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 single column with a clear value for the subsequent filtering / sorting is still preferable. Color - a thing-vested. In the summary table, for example, you do not drag.
- Get into the habit of adding to your table automatic filters (Ctrl + Shift + L), Securing areas. Table desirable sort. Personally, I have always infuriated when I received each week from the person responsible for the project, a table, where there was no filter and secure areas. Remember that these "little things" are stored for very long.
Merging cells
Use merge cells only if without it in any way. Merges it very difficult to manipulate the range in which they are included. Problems arise when you move cells, when you paste cells, etc.
Combining text and numbers in one cell
Painful impression cell containing a number, augmented behind the text constant "RR." Or "USD", entered manually. Especially if it is not a printing plate, and a regular table. Arithmetic operations such cells naturally impossible.
The numbers in the form of text in a cell
Avoid storing numeric data in a cell in the text format. Over time, some of the cells in this column, you will be in text format, and some in the ordinary. Because of this problem will be with the formulas.
If your table will be presented through an LCD projector
Choose the most contrasting color combinations and backgrounds. It looks good on the projector dark background and light letters. The most awful impression red on black and vice versa. This combination of extremely low-contrast look at the projector - avoid it.
Excel sheet in page mode
This is the mode in which Excel shows how the sheet will be divided into pages when printing. Page Borders highlighted in light blue. I do not recommend to work constantly in this mode, which many do, because in the process output data to the screen part of the printer driver, and is in Depending on many factors (e.g., a network printer and is not currently available) fraught podvisanie imaging process and recalculation formulas. Operate normally.
Even more useful information about Excel, please see Online Denis.