Excel is everywhere in the world of work because it lets you collate and analyse data so quickly.
If you’re looking to improve your productivity, improving your Excel skills would be a great place to start which will pay benefits for years to come.
If you’re a novice with Excel and have 10 minutes to spare, learning it’s data analysis tools is the place to start. Obviously, if you have more time you might want to consider a more structured Excel course.
Let’s dive in.
1. Sorting Data
Data sorting in Excel helps you to quickly sort your data in a number of different ways to spot trends and correlations.
Excel offers a range of sorting options. You can arrange your data no matter if its text data, numerical data, or date and time-related data.
Below is a screenshot of the raw dataset that we’re going to use to illustrate this article.
Let’s start by sorting the data on the data on it’s first column “OrderDate”.
To do this, select the complete range of data and go to the Sort option (marked as 2) under the Data tab (marked with a 1).
After clicking on the Sort button, a new window will appear asking for your requirements.
In this case, let’s try the following. The numbers are referenced in the picture below.
1) Sort by: OrderDate
2) Sort On: Cell Values
3) Order: Newest to Oldest
After clicking OK, data will be sorted as:
The Sort option can arrange the data based on multiple criteria.
Let’s look at an example sorting the data based on the columns, “Region” and “Total”.
After selecting the Data tab and clicking the Sort option (as we did above), you can input the data below. Again the numbers are referenced in the image below.
1) Sort by: Region
2) Sort On: Cell Values
3) Order: A to Z
Click to “Add level” for the second input.
4) Sort by: Total
5) Sort On: Cell Values
6) Order: Largest to Smallest
After clicking OK, the data will be sorted as,
Obviously, you can extend this and filter on three on more columns if required.
2. Filtering Data
Filters allow you to look at a sub-set of your data very easily, without deleting any of it.
This allows data analysts to simplify large datasets so that they can to focus on the data that are relevant to the problem they are solving.
As with sorting, Excel gives you great flexibility when it comes to filtering the data. You can filter your data based on text, numbers, dates, and other custom requirements.
To filter your data you need to have it in a table in Excel. You know if your data is in a table as it will show small down arrow buttons at the top of each column. See the red box in the screenshot below.
To format the data as a table, select the entire data range and press CTRL+T.
Another way turn your data into a table is to select all the headers and click on the Filter button (marked as 2) under the Data tab (marked as 1) to apply filter to your data.
In the below example, the original data has been filtered to show only orders related to item “Pencil”. Click on the down arrow beside the “Item” column to open the filter menu. In the menu uncheck all the items except that you want to keep, in this case, “Pencil”.
After clicking OK, the original data will be narrowed down to show only orders related to the item “Pencil”.
Pay attention to the red box, Excel has also summarized the results for you, there is a total of 13 orders related to “Pencil” out of 43 orders.
One pro-tip to filter out multiple items from a huge list that does not fit on the screen is to simply type them in the search bar and then select them.
3. Conditional Formatting
Conditional formatting helps you to visualize your data.
Visualising data, makes data much quicker and faster for people to understand.
Let's see conditional formatting in action. In the below example, we want the “Total” order value to be highlighted when the value is between 500 to 2000.
Select the “Total” column and go to the Conditional Formatting menu (marked as 2) under the Data Tab (marked 1).
Hover your cursor over the “Highlight Cells Rules” (marked as 3) to open the sub-menu and select the “Between” option (marked as 4).
After clicking the “Between”, a new window will open asking for specific criteria.
Enter the range of values that you want to highlight, in this case, 500 and 2,000 are entered.
Select the highlighting colour of your choice and click OK.
While inputting the values, Excel allows you to preview the changes.
Now all the order values between 500 and 2,000 are highlighted in green color.
Conditional formatting contains lots of other ways to visualize your data.
Obviously, graphs are a more advanced way to visualize your data which is beyond this article. Excel contains lots of different types of graphs so that would be a great place to go if you’d like to take your skills a step further again.
Excel offers a lot of ways to analyse your data.
Recent updates in Excel have even made it possible to connect with external data sources and transform the data the way you want with the help of Power Query and Powe Pivot giving access to even more data for advanced users.
Hopefully this article has inspired you to learn more about Excel’s data analysis tools.