Excel is fully loaded with features and functions that can help you gather, organize, manage, and sort data just the way you need. Most of the time users ask why use Excel when there are so many data analytics tools.
Simply, the answer to this is Excel is the biggest spreadsheet program anyone can use. With an easy-to-use interface, Excel allows you to avail so many features without any cost. More than that, Excel analytics tools are great for analyzing data to save time and effort.
Use Power Query to Collect Data
You need to collect data before analyzing it. Fortunately, Excel lets you experience many options to get and organize data. Using Power Query, you can easily manage to collect data from several sources such as databases, CSVs, XML, or XI files.
Once the data is collected from different sources, you can easily sort data as per your needs. With Power Query, you can form data according to what you need. Moreover, you can even merge data from different files into one file. Suppose, you have more than 100 Excel sheets, you can combine them into one workbook to save time.
Apart from this, you can even collect data from other apps into Excel such as you can collect data from SalesForce into Excel to analyze it.
Use Auto Filter to Clean Data
Once the data is collected, the next step is to clean it. you can clean data by using multiple approaches, such as:
Clean the Formatting
Your data could be out of space and for this; you can use Auto Filter which helps you in finding values. Suppose, you have a list of names that include birthdates appearing in front of each name. Normally, the birthdates appear in number format. However, in this example, we need to pretend the birthdates are in the format of “YYYY-MM-DD”.
Below is the process you can follow to clean formatting:
- Go to the Data tab.
- Choose the Auto Filter option.
- Click on the Custom option given in the drop-down menu and put the formula:
=LEFT(A2,4)
- Using this formula, you can find out the 4 digits from the birthdate.
- Choose the Auto Filter option once again and now click on “Date” as the category and Custom as the criteria.
- Put the formula:
=RIGHT(A2, 2)
- Using this formula, you can bring out 2 digits from the birthdate. Moreover, you can filter out each birthdate that does not have four or two characters.
Remove Duplicate Rows in Excel
Sometimes, you may come up with rows in a list, and here’s how you can remove them:
- Go to the Data tab.
- Click on the Remove Duplicates option given in the drop-down menu.
- Check the “Unique” box. And click the OK button.
- You will see the “Remove Duplicates” dialog box appears. From this, you need to check all the options.
- Click Ok.
In the end, you will see all the duplicate rows are deleted from the sheet.
Remove Blank Rows in Excel
Your Excel spreadsheet may have some empty rows that you need to delete. For this, you can follow the steps given below:
- Go to the Data tab.
- Click on the Remove Empty Rows given in the drop-down menu.
- Check the “Unique” box. And press OK.
Conditional Formatting
Conditional formatting is a handy function used in Excel that lets you highlight or even hide cells. You can apply rules in one or more cells in the same sheet. You can use it for highlighting, duplicates, or patterns in data.
For instance, you need to highlight the Year_Birth values greater than 1987 in the given dataset. Choose the Year_Birth column and click on the Conditional Formatting option.
Now, choose Highlight Cells Rules and then Greater Than option. You will see an editor:
Type 1987 and press OK.
You will see the cells turn red that contain values greater than 1987. You can even adjust the conditional formatting rule as in:
Choose Conditional Formatting and then Conditional Formatting Rules Manager.
Now, you can easily make new rules or even make changes to the current ones. You can simply apply more than one rule on the spreadsheet.
XLOOKUP
Here comes another one of the greatest Excel analytics tools XLOOKUP. It is more like a combo of VLOOKUP and HLOOKUP. To look up a value from a given range, XLOOKUP can be used vertically or horizontally. With this tool, you can simply choose a range as a lookup table and return a “looked up” value to a cell.
For this, the syntax is given below:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_model], [search_mode])
Let’s understand it with an example:
Suppose that you want to look up the Year_Birth based on ID value. In cell AD2, type an ID value such as 8755. In the AE2 cell, you can type the XLOOKUP formula:
=XLOOKUP(
You can add AD2 if you need to look up the function. In the lookup_array column or row, you may have A2:A2241 as it gives an array of IDs.
The return_array is the column or row having values that are needed to return that’s why you can choose B2:B2241 because it gives Year_Birth values.
Below is the complete formula:
=XLOOKUP(AD2,A2:A2241,B2:B2241)
Wrap Up
Your data analysis experience with Excel would be entirely magnificent. Cleaning and exploring data with Excel further lets you add advanced data analytics abilities.
