Excel Analysis Tutorials
Here is a sneak preview of how much you can accomplish using quick analysis tools in Excel!
So what is data cleansing you ask?
We get thrown data at us from external accounting systems, Enterprise Resource Planning systems (ERPs) or downloaded extracts from the company server and most of the time it is not imported in an Excel friendly way.
This drives me nuts! But the beautiful thing with Excel is it provides a multitude of ways with its analysis tools! You can use it for correlation analysis, trend analysis in Excel you name it!
Let us go over them one by one!
Power Query is a free add-in created by Microsoft for Excel 2010 (or later) and you can download and install it for Excel 2010 and 2013 here:
Download Power Query here
How to Install Power Query 2010 here
How to Install Power Query 2013 here
In Excel 2016 and newer version it comes built in the Ribbon menu under the Data tab and within the Get & Transform group. So no need to do anything here as you have the awesome Power Query out of the box!
Power Query allows you to extract data from any source, clean and transform the data and then load it to another sheet within Excel, Power Pivot or the Power BI Designer canvas.
One of the best features is to Unpivot Columns. You will be amazed on how easy this works!
What that does is transforms columns with similar characteristics (e.g. Jan, Feb, March…) and puts them in a unique column or tabular format (e.g. Month), which then allows you to do further analysis using Pivot Tables which was not possible before unpivoting.
It is so common to have this format from data extracts. Especially if for example you got a csv file (comma delimited file) downloaded from a table in your database. It is not just conducive to Excel data analysis.
Here is how this is done:
Find & Replace
The Find & Replace feature or CTRL+H shortcut allows you to amend your data in seconds. Imagine you had thousands of rows of data that was downloaded from an external system with the wrong date. A simple CTRL+H will save you heaps of time!
Keep this Excel shortcut handy in your pocket so that you can use this anytime when you need to search and clean repetitive errors in your data!
Find Errors with Go to Special Constants
You can easily check this by highlighting that column and pressing CTRL+G to bring up the Go To dialogue box (or by choosing from the menu Home > Find & Select > Go To…)
Then you need to choose Special > Constants and select the constant that you want to find in your column.
You would be surprised that CTRL+G could be used for this right? I used to think that this is just to jump in navigation over your worksheets!
In our example you will need to only select the Text box and de-select the other boxes and press OK. This will highlight the cells that contain text and you can begin to format these cells.
Find Blank Cells In Excel With A Color
One of the most common things you may encounter are blank cells in your Excel data which can hinder your analysis, especially if you are using a Pivot Table to analyze the data.
To find these annoying blank cells in Excel you will need to highlight all your data set (CTRL+*) and bring up the Go To shortcut: CTRL+G > Special > Blanks.
This is another cool trick with CTRL+G! Imagine it doing the dirty work for you to find all the blank cells, then highlighting them for you so that you can quickly make the manual fixes.
Then you can fill in the blank cells with a color red and filter by that same color to drill down to these blank cells and take some formatting action.
Remove Duplicates in an Excel Table
The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them.
Not to worry, Remove Duplicates to the rescue! Say goodbye to manual removals!
STEP 1: Click inside your Excel Table and select Table Tools > Design > Remove Duplicates
STEP 2: This will bring up the Remove Duplicates dialogue box. Select only the Column box that contains the duplicates that you want to remove and press OK
With just these 2 simple steps, your duplicates are instantly removed!
Text To Columns: Dates
No worries! Text to Columns to the rescue! It will do the conversions for you.
Do take note that if the dates are not converting properly after you do the Text to Columns, it may be that your computer’s Region formats need changing.
Go to the Region settings in your Control Panel and make sure that the Short Date is in this format: dd/MM/yyyy
Using Formulas To Clean Data
– The TRIM Formula
– The REPLACE Formula
Top 10 Tutorials
- Create a Data Entry Form in Excel– Whenever I wanted to input data for my Tables in Excel, I would have a long time inputting the records one by one. I discovered a handy trick in Excel that can turn my Table into a handy Data Entry Form!Read more
- How to Remove Formulas in Excel– There are times when I have an Excel worksheet full of formulas and I want to hard code the results and remove the formulas completely.Read more
- Excel Number Formats – Thousands & Millions– Large numbers in Excel can be formatted so they can be shown in “Thousands” or “Millions”.Read more
- Add Custom Symbols With Your Numbers in Excel– In our example below we want to enter a % Variance column in our data and insert the symbols ?? to show a negative and positive variance.Read more
- Dynamic Data List using Excel Tables– Excel Tables have many great features to them and one of them is their ability to create a dynamic drop down list.Read more
- What Microsoft Excel Version Do I Have?– I will show you a few cool ways where you can determine the Excel Version right away!Read more
- Clear a #REF! Error in Excel– When your Excel workbook has formulas that are linked to other workbooks/cells that have been deleted, you will get a #REF! error.Read more
- Excel Table Slicers– In Excel 2013, Slicers have been extended to be used with Excel Tables. WOW!Read more
- Top Excel Data Cleansing Techniques– Data cleansing is an important activity within Excel and one that we find ourselves doing day in day out, sometimes without even knowing itRead more
- How To Create A Custom List In Excel– A Custom List in Excel is very handy to fill a range of cells with your own personal list. It could be a list of your team members at work, countries, regions, phone numbers or customers.Read more