A data analyst needs to manage data on a daily basis. When starting our as a data analyst, getting to grips with Excel is a vital skill that will save you hours of time! In this article, we look at some basic excel functions that every data analyst needs in order to get by.
You can’t get a handle on data without having some way of organising or categorising it. The sort function in Excel helps you quantify what is going on. You can sort data in a variety of ways – numerically, alphabetically, or by category. It can be done very quickly, and brings clarity and organisation to your work.
How to sort data
- Highlight the rows or columns to be sorted.
- Go to the Data tab and click on Sort.
- In the options box, decide how you want your data sorted.
You can also use the shortcut keys ALT + H + S to do this. Or you can right-click on the table, select Sort and your intended method, e.g. Largest to Smallest.
Applying filters to your data makes it easier to work with. With the aid of this function, you can subgroup your data as much as you like. It lets you narrow down your dataset, so you only view the information you need.
How to apply filters
- Click on any cell in your spreadsheet.
- Go to the Data tab and click on Sort.
- Click on any of the drop down menus that will appear next to each column heading. Then choose the option you want to filter out of the data.
- A filter sign will appear on the column heading showing the data you have selected has been filtered out.
Alternatively, use the shortcut key ALT + H + S + F and use the drop-down menu for the column you want, then choose your filter option. Or right-click the selected area and select the filter options.
Visuals are a great way of getting a feel for your data, and are especially useful when communicating to colleagues or making presentations or reports. Excel charts are a flexible way of expressing your data, whether in line, bar, or pie form!
Your guide to inserting a chart:
- Select the cells that you want to show in graphic form. You may include the columns and row headers.
- From the tab labelled “Insert”, click the Chart option, and select the type of chart you want.
- The chart you choose will be added to your worksheet!
This is one of the most used functions in Excel, as it helps you make logical evaluations based on your data.
Each IF assertion can yield two results: true or false. You use the formula to instruct Excel on what to do if the answer is true, and what to do if it is false.
How IF formulas are used
The IF formula takes the format =IF(logical test, result if true, result if false). Some logical operators you can use with the IF function include:
= (equal to)
<= (less than or equal to)
<> (not equal to)
> (greater than)
>= (greater than or equal to)
< (less than)
One example of a useful IF formula is to check if an item is within budget. If your item cost is in cell D2, and your budget for the item is in cell E2, then you can use the formula
=IF(D2<=E2,”On budget”,“Over budget”)
This will return a text result of “On budget” if the costs are less than or equal to the budget figure, or “Over budget” if the costs have exceeded the budget.
When working on a spreadsheet you might need to add cells only if a certain condition is met. The SUMIF function adds all numbers in a range of cells based on one criterion. When you are required to add more than one condition, one should use SUMIFS instead of SUMIF.
How to use SUMIF
Suppose you wanted to calculate the total value of Kate’s sales. If the sales agent name is in column A, and the value of each sale is in column B, you could use the formula =SUMIF(A:A,“Kate”,B:B). This adds all the values in column B that correspond to the word “Kate” in column A.
The Excel TEXT Function is used to convert numbers to text. Essentially, the function will convert a numeric value into a text string. Use the TEXT function to:
- Display dates in a specified format.
- Show numbers in a specified format or in a more legible way.
- Combine numbers with text or characters
How to use text formulas
A simple example is the formula to show the date in day/month/year format.
Other common useful functions for data analysts are LEFT and RIGHT.
First, you have the Left function if you are wanting to extract the leftmost characters from a string, e.g. =left(D5,5) selects the first 5 characters from cell D5. Similarly, you can also use the Right function to extract the rightmost characters.
Pivot tables are useful to review, sort, organize, regroup, and calculate large amounts of data from a table. It helps you to convert from columns to rows and vice versa. It also allows clustering by any column or field, so you can use improved estimates on them.
How to create a pivot table
- Select the cells you want to create the pivot table from.
- On the Insert tab, go to PivotTable.
- Insert Pivot Table. There is a dialog box that will appear, and the Excel will automatically choose the statistics for you. The default position for the brand new pivot table will be a new worksheet. You can also choose to insert the pivot table into clear cells in your current worksheet.
- A pivot table field box appears on the right of the page, and from this you can determine what you want to show.
You can also use the shortcut ALT + N + V + C.
Conditional formatting is an incredibly useful tool for highlighting interesting data and trends. Excel spreadsheets can get confusing especially when they are large, and a conditional format helps by changing the appearance of cells.
For example, you could set a rule that cells containing your boss’s name are highlighted bright blue, so that you know those items are waiting for feedback. You can set a rule to highlight cells in a column bright red if they exceed the budget. Or you can apply a gradient of colour so you can see which items are more or less profitable.
How to use conditional formatting
- Select the cells you want to format.
- Once you are in the Home tab, you can choose “Conditional formatting” under the styles group, and then select New Rule.
- In the New Formatting Rule window, select a rule type and edit the rule description.
- Set your format and click OK.
You can switch between the Font, Border and Fill tabs. Play with different options such as font style, pattern colour and fill effects to set up the format that works best for you.
You can also use the shortcut key: ALT + H + L to bring you to the conditional formatting function.
Becoming an Excel Master
Every data analyst strives to simplify data, and this task is so much easier when you are familiar with Excel. Don’t be afraid to begin to try these different functions. Devise some simple spread sheets and start to play. The more you practise, the more confident you will be when working with your data to produce great results!