I just came to the realization that Microsoft Excel is one of the most commonly tools used at the workplace. Not for anything else but for data analysis. I have never thought of it this way. Given my background in Finance, we use Excel for bragging rights depending on how many formulas you created and run on excel to make your your financial models run.
Data Cleaning
Before any analysis, most data requires to be cleaned or adjusted in a way that it is easily used for any analytical work. Data collection sometimes comes with variant responses that make it hard to use the data as it is. making it necessary to clean data before usage. A really cool tool I learnt this week was Data Validation. This helps restrict the type of feedback you get per column making it easier when you use the data later. An example would be to restrict product categories that one can enter or maximum and minimum limits. Enforcing it is a easy as:
- Selecting the necessary cells
- On the ribbon, click Data then Data Validation
- Under Allow, select List
- In Source, type values separated by commas
- Click OK
Then depending on your data apply custom validation.
Filtering
Another important tool in Excel has to be filtering, thinking of it now I should have started with this one.
This gives an over view of how the data looks like, if you actually need to apply changes to the data or leave it as it and so on. I believe it is impossible to use excel especially when interacting with tons of data and not use filtering.
It's like a sneak peek of the data displaying only the rows that meet criteria while the rest stay hidden.
How do we exactly do that:
- Click anywhere in the dataset
- On the Home tab, go to Sort and Filter, Filter
- Click on a dropdown on the header row
- Choose the values to show or use
Filtering could be used to select data from a certain region, a specific limit or a group, all depending on your criterion.
Data Formatting and Cleaning
Number formatting
This function of Excel would come in handy when you probably are trying to perform a mathematical operation but it just wouldn't work. Chances are the formatting is appropriate of the data type. A hint that would help to determine if the formatting is appropriate is; numerical data aligns to the right while text to the left. However, this sometimes isn't enough.
Let's now get to how it is done:
Percentage formatting
- Select cells
- Home > Number group> Click % symbol
- Numbers convert to %
Currency formatting steps
- Select the cells with numbers
- Home tab > Number group
- Click the dropdown arrow on the number format box
- Select Currency or Accounting
- Change the currency by clicking the small dialog launcher
A simpler way to do it on the Home tab to go to the number segment and click on the drop down on the bottom right and any changes can be made from there
Conditional Formatting
This is a useful tool especially if you need to pay attention to a criterion or a group of data. It highlights cells automatically based on the rules set, helping with locating with trends and that which you wish.
An example is when handling credit data and if the loan is way past due time, conditional formatting can be used to categories the different classes of default.
- Go to Home > Conditional Formatting
- Choose a rule type:
- Highlight Cell Rules (Greater than, Less than)
- Top/Bottom Rules
- Data Bars, Color Scales, Icon Sets
- For example, select Highlight Cell Rules > Less Than
- Enter a value
- Choose a formatting style (e.g., green fill)
- Click OK
There is so much that one can do when it comes to data analysis. However, I have focused on the ones that I use a lot more, but on the contrary they don't seem to be that obvious such as removing duplicates and data sorting.
Hope this makes data analysis on Excel a bit lighter and faster for you as it did for me.
On a lighter note, I would always recommend videos incase anything isn't clear, practice does it better than any other way.
Top comments (1)
your post is interesting
I would like to get to know you better. Would you please contact me? t_g_@kanelim1997