12 of the most common problems with Excel and their solutions




We present to you a guest post from which you will learn how to avoid the most common problems with Excel, which we create ourselves.
Readers already familiar with Layfhakera Batyanova Denis, who shared with us the secrets of Excel . Today, Dennis will talk about how to avoid the most common problems with Excel, we often create themselves independently.

Just specify what material the article is intended for novice users of Excel. Experienced users already incendiary danced on these rake times, so my task to save from this young and inexperienced “dancers”.
You do not give table headings

Many tools Excel, for example, sorting, filtering, smart table, pivot tables, – mean that your data contains column headers. Otherwise, you will either not be able to use them, or they will work is not entirely correct. Always take care to your table contains column.
Blank rows and columns within your tables

This is confusing Excel. Faced with an empty row or column in your table, he begins to think that you have 2 tables instead of one. You will have to constantly correct him. Also, do not hide your unwanted rows / columns within the table, it is better to remove them.
On one sheet is more than one table

If this is not the tiny tables containing references values, so you should not do.

You will be inconvenient to fully work with more than one table on the sheet. For example, if a table is on the left and the other to 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 the fixing areas, as well as one of the tables have to constantly look for and produce extra manipulation to get up on her table-pointer. It you want?
One type of data are placed in different artificial columns

Very often, people who know Excel rather superficial prefer this format of the table:


It would seem to us harmless format for storing information on sales agents and their penalties. This layout of the table is well perceived by the human eye, as it is compact. But, believe me, it’s a nightmare – trying to retrieve data from these tables and get subtotals (aggregate information).

The fact that this format contains two dimensions: to find something in the table , you must specify the string fingering branch, group and agent. When you find the desired drain, then you have to look for the column already, since many of them are here. And this “two-dimensionality” greatly complicates the work with a table and standard tools for Excel – formulas and pivot tables.

If you build a pivot table, you will find that you can not easily get the data for the year or quarter, as the figures are carried on different fields. You do not have a single field in terms of sales, which can be conveniently manipulated and there are 12 individual fields. We’ll have to create a separate hands calculated fields for the quarters and years, though, whether it’s all in one column, a summary table would have done it for you.

If you want to apply the standard summation formulas type SUMIF (SUMIF), SUMIFS (SUMIFS), SUMPRODUCT (SUMPRODUCT), you will also find that they are able to work effectively with the layout of the table.

The recommended format of the table looks like this:

right to work in Excel
Diversity of information on different sheets of the book “for convenience”

Another common mistake – is having some standard format of the table and the need for intelligence on the basis of these data, deliver its separate sheet in the workbook Excel. For example, often create separate sheets for each month or year. As a result, the amount of work on the analysis of the data is actually multiplied by the number of created worksheets. It is not necessary to do so. Collect information on one sheet.
The information in the comments

Often users add important information that they may need, in a comment to a cell. Keep in mind what is in the comments, you can only view (if you can find). Pull it into the cell difficult. Recommend better allocate a separate column for comments.
Mess with formatting

Definitely not add to your table any good. It looks disgusting for people who enjoy your tables. At best, it does not give value, at worst – will think you are not organized and sloppy in business. Aim for the following:

Each table should have a uniform format. Use formatting smart tables . To reset the old format, use the cell style “Normal”.
Do not select the row or column color entirely. Highlight the style of a particular cell or range. Provide a “legend” of your discharge. If you select a cell to produce in the future with them some operations, 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 subsequent filtering / sorting still preferable. Color –vested thing. In the summary table, for example, you do not to drag.
Get into the habit to add to your table automatic filters ( Ctrl + Shift + L ), freeze panes. 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 filters and secure areas. Remember that these “little things” remembered a very long time.

Merging cells

Use merge cells only when without it in any way. Merged cells it very difficult to manipulate ranges to which they belong. Having trouble moving cells, when you paste cells, etc.
Combining text and numbers in one cell

Painful impression cell containing a number, supplemented behind the text constant “RR.” Or »USD», entered manually. Especially if it is not a printing plate, and a regular table. Arithmetic operations with such cells naturally impossible.
Numbers in the form of text in a cell

Avoid storing numeric data in text format in the cell. Over time, part of the cells in this column, you will be in text format, and some in the ordinary. Because of this, there will be problems with the formulas.
If your table will be presented through the LCD projector

Choose the most contrasting color combinations and background. Looks good on the projector dark background and light text. The most awful impression red on black and vice versa. This combination of extremely low-contrast looks on the projector – avoid it.

Page Mode sheet in Excel

This is the mode in which Excel displays as a leaf is divided into pages when printing. Page Border highlighted in blue. I do not recommend to constantly work in this mode, which many do, because in the process of data output to the screen printer driver is involved, and it depends on many factors (eg, network printer and is not currently available) hang fraught process visualization and conversion formulas. Work as usual.


Even more useful information about Excel can be found at the website of Dennis .


In: A Technology & Gadgets Asked By: [20418 Red Star Level]

Answer this Question

You must be Logged In to post an Answer.

Not a member yet? Sign Up Now »

Star Points Scale

Earn points for Asking and Answering Questions!

Grey Sta Levelr [1 - 25 Grey Star Level]
Green Star Level [26 - 50 Green Star Level]
Blue Star Level [51 - 500 Blue Star Level]
Orange Star Level [501 - 5000 Orange Star Level]
Red Star Level [5001 - 25000 Red Star Level]
Black Star Level [25001+ Black Star Level]