DATA MANAGEMENT in Excel

Data Management is one of the key features provided by Microsoft Excel. There are various techniques which promotes efficient data management. Let us talk about these techniques in detail.

Organising

Organising your data into groups can help you manage your data more efficiently and effectively. You can choose to view only those groups or sub-groups which you want to see at that instant and hide others giving you a clearer picture of what you are looking for.

One way of organising your data is Grouping your data into relevant groups:

Grouping Cells

If you are working on a worksheet containing lots and lots of data. It becomes tedious to navigate around the worksheet. It may be a good idea to group the cells to organize your data for efficient workflow.

Excel can organize data in groups, allowing you to easily show and hide different sections of your worksheet.

How to group cells?

• Select the table or part of the table or your desired area to be grouped

• Go to Data Tab –> Click on Group under the Outline group

• A dialog box will appear asking whether the selection is to be grouped by Column or Rows.

Here we have selected to group by column. And then click on OK.

You’ll now notice that your selection is now grouped.

You can also hide the group by clicking on the – (minus) button.

In this way you can organize your data as well as show or hide the relevant data.

Cleaning

Sometime your data may not be consistent. It may have unwanted whitespaces or some unusually formatted data.

Cleaning your data can prove to be very efficient for formulas and your queries, resulting in error free operations.

Remove Whitespaces in a cell

How to remove spaces within a text/number in a cell or remove trailing or leading whitespaces?

To remove extra spaces in a text field

We will use a helper column to first trim out the text and then copy the column to the original column and save the changes.

1. Choose a helper column to work with
2. Apply the formula =TRIM(cell_no) to the helper column

3. Copy the formula down the cells to the desired number
4. Select the helper column and paste to the original column

To do this:

• First select the contents from the helper column and copy (CTRL+C)
• Select the cells in the original column to be replaced.
• Right click on the new selection
• Click on the paste values (icon with 123 written on it) icon.
• Delete the Helper Column

Voila! You now have removed the spaces.

Let us now Remove extra spaces from numbers

The easiest way to get rid of excess spaces is using the standard Excel Find & Replace option:

• Press Ctrl + Space to select all cells in a column.
• Press Ctrl + H to open the “Find & Replace” dialog box.
• Press Space bar in the Find What field and make sure the “Replace with” field is empty.
• Click on the “Replace all” button, and then press Ok. 

The above examples can give you consistent and clean data to work with, greatly reducing the chances of errors during any computation.

Subtotals

Subtotals can provide a great overall experience with your data, which automatically organizes data based on given fields and calculates the subtotals of every category by grouping them.

Before performing a subtotal, always sort your data accordingly.

Subtotal Function

It is a very powerful function in excel which can calculate functions like SUM, COUNT, AVERAGE, MIN, MAX to summarize your data on a group by automatically categorizing them.

It will create a hierarchy of groups, known as an outline, to help organize your worksheet.

Caution: Your data must be sorted before applying SUBTOTAL

Let’s learn to apply SUBTOTAL by doing an example

Consider the following data

• Suppose we want to have the SUBTOTAL for price of each model grouped by Company.

Click on any cell containing the company name.

• Under the Data Tab, click the SUBTOTAL button.

A dialog box will appear.

In this dialog box you can select for what change you want to update the Subtotal Values.

In this example we are adding up the prices of each model. Hence, we are using the sum function.

You can choose count if you want to have a count of the data.

Now click on OK.

• After clicking on OK, you’ll find your sum of the prices for each model grouped automatically with also the Grand Total of the data.

This method can be very useful when you are dealing with large sets of data. It can help you save considerable amount of time and can make your work automated and efficient.

Managing Duplicate/Empty Cells in Excel

Sometimes a formula can throw an error when there is an instance of an empty cell which was required by the formula to have some specific format values.

To resolve this issue, we can initialise a cell by 0 (for numeric data) or “Not Available” in case of textual data.

Steps to Select all the Blank cell and apply a Default value:

1. Select the whole data set which contains blank cells.
2. Press F5, a dialog box appears
3. Click on Special ->This opens the Go To Special dialogue box
   
4. Select Blank and Click OK
   

This selects all the blank cells in your data set. If you want to enter 0 or Not Available in all these cells, just type it and press Ctrl + Enter.

NOTE: if you press only enter, the value is inserted only in the active cell.

Now, let us tackle the issue of Duplicate data in cells.

You can either delete or highlight a cell containing Duplicate data.Highlight Duplicate Data:

Select the data and under the Home Tab–> Conditional Formatting –> Highlight Cells Rules –> Duplicate Values.

Now all your duplicate values will be highlighted and you can perform the required operation on them.

If it is needed to delete the duplicate data then follow the below steps:

• Delete Duplicates in Data: 

1. Select the data and Go to Data Tab –> Under the Data Tools category -> Click on Remove Duplicates.

2. If your data has headers, ensure that the checkbox at the top right is checked.

3. Select the Column(s) from which you want to remove duplicates and click OK.
   

If you want to retain your original data, first copy the data into another location and then perform the Delete operation.

That’s all for our best practices and tips for effective Data Management.

Hope you’ve learned a lot.