How to organize data in Microsoft Excel

The data organized well in Microsoft Excel improves accuracy, facilitates the most effective analysis, which is easier not only for humans but also machines to understand and explain. You follow these five tips guaranteed to make your life easier, your colleagues are happier, and your data.

1

Use the fields (columns) and records (rows)

Fields and records are essential elements of the Excel network. Each column should be a field, which is a fixed type of data, such as names, email addresses, or aesthetic sales. On the other hand, each row must have a record, containing different types of data around one element.

Review the example below, where each column is a unique field for consistent data types, such as sections (text values) in column B, cost prices (accounting values) in the E., and units sold (numerical values) in the column G. each row, however, is a record of an individual element.

Think about it this way. When you click Double Click on the packing handle in the lower right corner of the cell that contains a formula, Excel applies the same principle lowest For all the remaining cells in the column. In other words, the program expects to have similar data to the material in a column, and therefore, the same formula applies to all elements in a field.

One way to make your data more clearly using this network structure is to ensure that the left field is an identifier, such as name, identifier, serial number, or similar thing, as you can then scan with an individual record to see the corresponding values.

To keep your data elegant and arranged, each field and record should be unique. Of course, you may have duplicate values, but if you scan lightly across a full row or down a full column, then there should be no full record or full repetitions.

2

Each cell must have one data point

The Surefire method is to break the linear structure of the Excel Data collection in cramming more than one data point into a cell.

The logical basis for this advice brings me back to my previous points – the cell must contain an individual part of the information that represents the field intersection and a record. Therefore, if you write more than one part of the information in a cell, you break this “flat” structure.

In this example, a separate price column should be added to the data to the variable pricing structure.

Since the data is not properly organized, when it is conceived as Excel plans, it turned into axi, or has been uploaded to the EXCEL Energy Editor, it does not work as expected.

Likewise, if you refer to these cells loaded in formulas or try to sort your data and liquidate you, it is likely that you end up with confusing and unreliable results.

The principle of a single cell-the data point also applies to the merging of cells. For me, combining the cells into the Excel is similar to the use of MS Sans Comic in an official word document – both should be avoided at any cost! Once one or more cells are combined, you are away from the EXCEL basic network structure, which means that you will cause more problems than you can. If the adjacent cells contain the same value, then there is nothing wrong! There is no need to merge them together.

The alternative to merge cells is to use the center through the selection tool. Although the affected cells appear to be built, cell structures and integrity are kept. However, I still suggest you to enter individual data points in each cell to avoid confusion.

3

Avoid columns and empty rows

Each set of one data in Excel should consist of adjacent lids. In other words, be sure that there are no completely empty columns or rows between fields or records.

There are many benefits to ensure continuity through a data set, and most importantly is to help understand where the data range begins and ends. Once you have an empty column or row in the middle of your range, you create a material break in the relationships between field entries and recordings.

The empty columns or rows can lead to problems when providing your data in the plans. Also, it is a guaranteed time, as you will need to define all cells in the data set manually when coordinated as an Excel On the other hand, to do these tasks using a completely adjacent data set, you just need to select one cell.

Even if you insert a new column or row filled on a later date, find a way to tell Excel that there is supposed to have a value, such as adding a column head or inserting a series of temporary zeros.

“But what about collecting columns or rows?” I hear you ask. It is true that the combined columns must be separated with at least one column, and the grouped rows should be separated in at least one row. However, this should not be empty: instead, turning them into valuable ranges by calculating sub -groups.

4

Form your data as an Excel table

One way to keep your Excel data structurally and walk with what the program expects to do is formate your data set as an Excel table. Regardless of its structural benefits, Excel tables offer many other advantages, such as automatic medium ranges, organized references, rapid sorting, filtering, and many others.

First, make sure that each column is a separate field, each row is a record, each cell has one data point, and there are no empty rows or columns. You will also benefit from the presence of heads of the columns in the highest data. Also, if there is more than one collection of data in your workbook, make sure that there is at least one column or row between them.

Next, select any cell in the data set, click “Format as a table” on the main page tab on the tape, and choose the table style.

Then, in the Create Table dialog box, check the “Table My Mess Mess”, and click “OK”.

The data has now been well coordinated as an Excel

5

Use one row for column heads

Adding a column head row to an Excel data set is a good way to provide a context of data in each column, which makes reading and understanding of information easier. The head row also provides an additional row for a filter button, which provides you with compulsion to add it to the first row of data and may cause confusion.

What’s more, the heads of the columns are clearly unable when your data is coordinated as an Excel table. In fact, when they refer to the heads of columns in formulas – also known as organized references – they are easier to understand and analyze from references or direct columns by public head names.

For example, it is clear that we see that this formula puts the cost price in the cost column of the total profit in the sales column to produce a net profit:

=[@Sales]-[@Cost]

That’s all good and good, but why should the heads of the columns be only one row?

One of the main reasons is that screen readers depend on clear mono -dimensional layouts to understand the relationships between columns and rows. In fact, multiple head ranks can complicate mobility, causing difficulty visual impairment in understanding the structure of your worksheet.

In fact, it is not only limited to screen readers and its users who will benefit from one head row-where other Excel systems prefer to be organized in this way. For example, if you format your data as an Excel table, you can only get one head row. In fact, if you have more than one, the second can be considered a row of data (register).

If your data set has already has two or more rows of your head, don’t worry – you can fix this using energy inquiries.

In this example, there are two heads of the column – the first is the general, and the second is the quarter.

As I mentioned earlier, the types of data are common in one column as a field with one head, and each row will be a separate record. Therefore, this is the way the data should appear when organized properly:

To achieve this, place the indicator in any cell in the data set, and on the house tab on the tape, click “Format as a table”. Then, choose a pattern.

After that, in the Create Table dialog box, Dismantle “My table has heads,” and click “OK”.

At this stage, Excel does not realize that you have two headscarfs, so to remove this confusion, in the table design tab, Dismantle “Row head.”

Now, you need to download the table at the Energy Inquiries Editor. After selecting any cell in the table, click “From the Table or District” in the data tab.

On the “Transfer” Energy Editor tab, click “Transpose” to effectively turn the tables of the table and rows.

As a result of this procedure, the head row has now become pillars in itself. Here, the row that formed the first column (the year) is now in column 1, and the row that formed the second column head (a quarter) is now in the column 2.

Now, fill any voids in your pillars by right -clicking on the head of the column and selecting Fil> Download.

You are now ready to promote the first grade to the heads of the columns by clicking on the symbol in the upper left corner of the network, and choosing “using the first row as heads”.

At this stage, some heads may be incorrect. Don’t worry about this now – you can change this later.

Next, select the columns that were by your head rows by holding CTRL while clicking on them. Next, right -click on the specified columns, and click “Cut the other columns”.

Note how each row contains a year, a quarter, a store number, and a profit. Double -click each column head to rename it, and click the codes to choose the correct data type.

Finally, click the upper half of the “Close and Download” partition button on the main page tab on the tape.

And there you have! Binary table with one head row, properly defined fields in each column, individual records in each row.

---

In addition to developing good practices when structuring your Excel data, it is just important to get rid of some bad -circutum format habits, such as manually alignment numbers, not using a recognized history format, and using different lines. Learn about these two fields, and you will be proud of your business!