Working frequently on a large amount of data can be devastating. As a data analyst, your main work is to examine business risks, get data and find factors affecting business. For this reason, it is essential to fetch accurate information that managers will use to make decisions.
Here are 50 essential excel skills for a data analyst.
1. Pivot Tables
Pivot tables are a crucial part of data analysis for more than 20 years now. At the moment, it is now impossible to imagine a business running without this tool. Pivot tables summarize, sort, reorganize, group, count, total or average any data in a given table. With a pivot table, you can quickly transform columns into rows and rows into columns.
You can also group by any field, whether columns and do any advance calculations. If you plan to analyze your data using excel, pivot tables are the most valuable tool to use.
2. Data Filters
Data analysts use filters to explore and sub-group data in a table quickly. Filtering helps to hide data that you do not need to use at that particular time. Applying filters helps to narrow down your data to make it easy and convenient. Data filters are effective, especially when you need to filter more than one column. Filtering is more fast and effective as compared to conditional formatting and sorting.
3. Advanced Charts
Advanced excel charts are a great way of creating compelling and accurate data for a business or a company. There are three types of advanced excel charts that you can use as a data analyst to impress your manager and create a rapport with them.
- Sparklines – These charts are small and lovely in visualizing trends like increase and decrease in value. They include line, column, and win/loss.
- Gantt charts – Data analysts use these popular charts to track the activities of a project against time. Gantt charts are called bar charts.
- Thermometer – Thermometer charts are great in visualizing the actual value and the target value of an activity.
These three charts will help you to build unique visualizations which will impress your boss.
4. Vlookup
The vlookup function is one commonly used and recognizable function in Microsoft Excel. Vlookup helps to look for a value in a given table and returns information from another column relating to that value. It works by combining data from different lists to one or comparing two lists for matching or missing items.
5. Worksheets
A worksheet is a collection of cells organized in particular rows and columns. Usually, each worksheet contains over 1048576 rows and 16384 columns where you can manage your information. Data analysts use worksheets to add and analyze their data on a given workbook. Once you open an excel workbook, there will be tabs at the bottom of the window where you can easily insert a new worksheet.
6. Operators
Operators in Excel specify which calculation to perform on a given distribution of values. Data analysts use these four types of operators daily in Microsoft Excel as follows:
- Arithmetic operators – Used for addition, subtraction, multiplication, and division.
- Comparison – Used when comparing values.
- Text – Used to join two or more texts to form a single text line
- Reference – Used to reference ranges in your Excel sheet
7. Graphics Data
Graphs are valuable tools in determining the mathematical relationship between two variables. Excel charts make graphical representations of a given set of data. Graphic representation of data involves using symbols such as bars and lines in a bar graph or a line graph. Data analysts will be able to predict the results and perform accurate analysis after seeing the visual image of the data.
8. Data Interpretation
Data interpretation is an essential skill for analyzing data. It helps to acquire necessary information from a pool of irrelevant ones. The management uses the data to make the right decisions concerning business performance, management, and future planning. Data interpretation is helpful for individuals, businesses, organizations, and basically for research and statistics.
9. Lookup Functions
The Lookup function in Microsoft Excel has default behaviors when solving specific problems that make it worthwhile to data analysts. It would be best to use lookup when you need to look in a single/multiple row or column and find another value from the same position in a second row or column.
There are two ways to use lookup as follows:
- Vector form
- Array form
Analysts prefer the lookup function because it is less restrictive, simpler to use, easier to audit, and allows for left-to-right and right-to-left procedures.
10. Number Series
Number series in Excel provide accurate inferences for data analysis and critical decision-making. You can represent the data in diagrams, charts, and graphs to describe dates, time, cost, and percentages.
You can easily add numbers to a row of data by dragging and dropping the fill handle to auto-fill a specific column with a series of numbers. Besides that, these number series organize your data to help evaluate your company's progress.
11. Macros
Macros entail a recorded set of actions saved so that they are easily found again. Doing the same set of actions repeatedly allows you to create a macro for doing all activities with a single command. Data analysts use macros to save time when they are working on a task repeatedly in Microsoft Excel. Remember, you can run a macro as many times as you want as long as it is recorded. Creating a macro generally involves recording your mouse clicks and keystrokes.
12. Flat Data Tables
Flat data in Excel is data that contains values in all the cells within a given table. Flat tables, therefore, play a role in eliminating the potential for redundant data. They are very convenient for data entry and analysis, primarily where the data is for use by many contacts. Proper planning of the structure of the tables enables the sorting of information from the existing data.
13. Data Sorting
Sorting data in Excel is an integral part of data analysis. You can arrange a list alphabetically, compile another from the highest to the lowest, or group rows by colors. Sorting helps a researcher organize, visualize quickly, understand their data better, and make the right decisions.
You can sort data by:
- Text (A to Z or Z to A)
- Numbers (largest to smallest or smallest to largest)
- Dates and time (oldest to newest or newest to oldest)
- A custom list, i.e., large, medium, and small
- A format that may include cell color, font color, and icon
14. Excel Shortcut Keys
If you do a lot of data analysis, knowing excel shortcut keys is vital in increasing productivity and speeding up your completion of tasks. Impress your colleagues and manager by being able to task without a mouse if you fancy yourself as an excel power user. There are more than 100 Microsoft shortcuts; they include:
- Save as: Alt -F-A
- Bold: Ctrl+B
- Create table: Ctrl+T
- Add filters: Alt-A-T
- Sort A-Z: Alt-A-S-A
- Sort Z-A: Alt-A-S-D
- Remove duplicates: Alt-A-M
- Save: Ctrl+S
- Close workbook: Ctrl+W
These great excel data analysis shortcuts increase efficiency and speed, thus improving your experience.
15. Data Validation
Data validation is a handy tool used to set validations on any data entered into your worksheet. It means that data is accepted only when it meets specific validation criteria. Otherwise, you will get an error message that the values are invalid and rejected. You can create data validation rules on decimals, whole numbers, lists, date, time, custom, and any value. This tool ensures that a business can completely trust the data produced by analysts to be accurate and useful. The different alert styles stop and warn the user from entering invalid data in a cell.
16. Quick Analysis
Data analysts use quick analysis in Excel to format their data into a chart, table, summary formula, highlighted figures, or sparklines with just a few clicks. Formatting data makes it possible to analyze your data quickly and easily instead of going through various tabs.
Tools that you can use for quick analysis include:
- Formatting – Highlight data using conditional formatting
- Charts – Chart the selected data
- Totals – Create standard summary formulas
- Tables – Summarize data in a table/pivot table
- Sparklines – Create mini charts placed in single cells
17. Power View
Power View is a data visualization technology in excel that allows you to instantly create interactive charts, graphs, maps, and others that light up your data. It is one of the three data analysis tools that are available in excel. Power view gives data analysts the option of creating data models and presentations that are more sophisticated. Power View lets you dig deeper into your data to find other great patterns for forecasting, clustering, and grouping.
18. Conditional Formatting
Conditional formatting in Microsoft Excel allows you to apply a specific format to cells that only meet certain criteria. This skill helps an analyst visually explore, emphasize, differentiate, and analyze data to detect critical issues and evaluate the trends quickly. The conditional format makes it easier to highlight a range of cells using data bars, color scales, and icons related to different variations in the given data.
19. Managing Page Layout
Managing page layout describes how an analyst can use a group of commands that control how a spreadsheet will appear when printed. Some of the most popular commands in excel are:
- Margins
- Repeated titles
- Headings
- Headers and footers
- Background
- Orientation and paper size
Other page layout options include page orientation, margin size, print titles, and page breaks. As an analyst, you should modify your workbook's page layout in a way that makes its orientation to be appealing. Adjust and format your workbook to make your data look more professional.
20. Flash fill
Flash fill helps you enter data faster and accurately. It works by making predictions on the rest of the data based on your first entry. The tool automatically fills your data after sensing the pattern you are using. Furthermore, this feature can also remove, insert, format, and reverse text, numbers, and dates. Data analysts use flash fills to help avoid typing errors, especially when dealing with complex formulas.
21. Text Formulas
As a data analyst, your understanding of formulas will help you design better spreadsheets with reduced errors and troubleshooting. Organizing your data to take advantage of formulas is a powerful way to capture and save a working solution. Text formulas will help you build the analysis in your head productively, hence providing more value to the business. Text formulas are vital for data analysis; data analysts should increase their skills with formulas.
22. SUMIF Function
Sumif function is among the most crucial Excel functions that sum up values that meet specific criteria. Sumif can only test one standard at a time, while sumifs test for multiple conditions. One main advantage of the sumif function is its ability to limit the number of spreadsheets so that you have all your data on a single sheet. The sumif formula uses the following arguments:
- Range – Contains a content of cells where you will apply the criteria
- Criteria – It is criteria used to determine the cells to add
- Sum range – The cells that are to add together
23. Sort
Most data analysts prefer sort; however, it is only available to Microsoft 365 subscribers only. As the name suggests, it sorts the contents of a column in ascending or descending order. The sorted array automatically updates after making changes to the original data. The syntax of sort =
- Array – Range containing the values you want to sort
- Sort index – Indicates the row or column to sort by
- Sort order –Represents number 1 for ascending and -1 for descending
- By col – Indicates the desired sort direction, whether actual or false
24. Countif
The countif function is another mega Excel function for data analysis that counts the number of values that meet specified criteria. Countif is used to count cells that have dates, numbers, and text. i.e., in financial analysis, countif can help count the number of times a manager exceeded their target. Countif function uses the following arguments:
- Range –It defines one or several cells that you want to count
- Criteria – It is tested against each of the cells in the supplied range
25. Sparkline
Data analysts need to understand the different types of charts and know their usage to create intuitive visualizations. Sparkline charts are one powerful and influential advanced excel chart that will help you make unique dashboards. Since sparklines are small in size, they can easily be embedded in data grids and dashboards to show the pattern of values like sales. The three different types of sparklines include line, column, and win/loss.
26. Essential Keyboard Shortcuts
Essential window keyboard shortcuts shave a moment off your task, which saves your time a great deal. That is why an analyst must know as many shortcuts as possible. Basic essential keyboard shortcuts include:
- Ctrl+Z: Undo
- Ctrl+W: Close
- Ctrl+A: Select all
- Alt+Tab: Switch apps
- Alt+F4: Close apps
- Ctrl+V: Copy
- Ctrl+V: Paste
Using shortcuts makes you efficient when handling tasks which boosts productivity. They make you do more with fewer efforts.
27. Removing Duplicate Values
Excel has a built-in tool that helps delete repeated entries in a given set of data. People who work with large data sets in Microsoft excel should be prudent enough to check for errors such as duplicates. Upon using the remove duplicates feature, the duplicate data will be permanently deleted. Most analysts, however, will copy the original data to a different worksheet to prevent losing vital information. Duplicates are annoying and can lead to misinterpretation of data.
28. Trim
The trim function helps remove irregular spacing from data, mainly when imported from other applications. Trim removes all the extra spaces except for single spaces to clean up the cells of a given worksheet. It has only one argument, which is the text from which you want the spaces removed.
29. Concatenate
The concatenate function combines values from multiple cells into one. Since concatenate prompts you for the different values to use, it is suitable for connecting other text parts. In data analysis, you may also need to split data from one cell into separate cells; concatenate function will help you do that. It uses the following arguments:
- Text 1- The first item to join can be a text value, call reference, or number.
- Text 2 – It entails the additional text items that you wish to join.
30. Countblank
The blank count function counts the number of empty cells in a given range of cells. It is among the excel statistical functions that are built-in and useful in highlighting blanks. During analysis, contents with blanks can be visually counted using the blank count tool. The syntax of count blank requires only one argument, which is the blank count range. The range represents the range of cells in which blanks to count.
31. Sumifs
Summits are a function in Microsoft Excel used, to sum up cells that meet multiple criteria. Sumifs sum values when adjacent cells meet criteria based on either dates, numbers, or text. The sumifs syntax relies on the following:
- Sum range: Cells to add
- Criteria_range 1: Range of cells to apply criteria 1
- Criteria1: Determines which cells you need to add
- Criteria_range 2, criteria2: Contains additional ranges along with their criteria
32. Rank
Excel uses the rank function to return the rank of a number in a list of numbers. You can use RANK.AVG to produce the average rank if more than one number has the same status. By default, data analysts use ranks to arrange data values in ascending order (smallest to largest) then label the slightest value as rank. In a worksheet, rank determines the position of a specific value in an array. This function uses the following arguments:
- Number: The value which you need to find the rank
- Ref: A list or array of numbers
- Order: A number that specifies how the ranking will happen; 0 for descending and 1 for ascending.
33. Value
In an Excel spreadsheet, values can refer to text, dates, numbers, or boolean.
- Text: Represents data such as high or low
- Dates: Shows calendar dates like 20 Nov 2018
- Numbers: Represents numeric data such as 15 or 20
- Boolean: It shows the results of a comparison which can be TRUE or FALSE
On the other hand, a value function converts a text into a number. The type of value depends on the data an analyst is referring to.
34. Unique
A data analyst uses the unique function in excel to return a list of unique values in a list or range. The values can be numbers, texts, dates, and times. Excel will automatically create the required size range after you press enter. You can also extract a list of unique values using an array formula, a pivot table, or a power query. The unique syntax relies on the following:
- Array: Range from which to extract the unique values
- By col: It shows how to compare and remove; by row is FALSE and by column is TRUE
- Exactly once: Consists of values that occur once
35. Len
When you want to count the characters in cells, use the len function for accurate results. The function counts letters, numbers, symbols, and all spaces in a given worksheet. A data analyst can count both characters in one cell and characters in multiple cells with this function.
36. Left/Right
When you use data from other resources, it is usually not ready for analysis, and data scientists have to spend more than 80% of their time cleaning up their data. According to a specific order for each operator, excel calculates the formula from left to right. You can pull a set of numbers out of a cell into another cell in the left function starting from the left point. The right function does the same, although it starts from the rightmost point.
37. Maxis
Maxis, just like minifs, allows you to match on criteria except that it focuses on the maximum number. The maxims feature is available on Windows or Mac if you have an office 2019 or Microsoft 365 subscription. The arguments of maxif are:
- Max range – The actual range of cells from which the user will determine the maximum value
- Criteria range – Set of cells to be evaluated with the criteria
- Criteria1 – Entails a number or expression that defines the cells being assessed
- Criteria range2 – Here, you specify additional ranges
38. Graphs
Graphs create a clearer picture of a set of data values as compared to tables. Good decisions are made based on the impact that the images provided. They allow managers to incorporate this information so that it is helpful in analysis and future statements. Types of graph charts in excel include:
- Column charts
- Line charts
- Pie charts
- Bar charts
- Area charts
39. Power Query
Power query is an essential excel tool that you should know to be a good data analysis whizz. The function makes importing and transforming data from different sources a simple task. Transformation involves cleaning and reshaping your data as needed; you can choose to remove a column, change rows or merge tables. With a power query, you can set up a query once then reuse it after refreshing.
40. Formatting Data
In data analysis, formatting refers to the general appearance or presentation of your task. Data formatting means that as you enter data in a new worksheet, StatsDirect determines the data type and applies the appropriate format. By default, all cells in a worksheet use the general layout, but a data format arranges the data fields for a specific shape. For instance, you can change the appearance of a number or format the alignment, font, and border of the spreadsheet.
41. Slicers
Slicers are used to filter pivot tables quickly or excel tables in Microsoft excel. Data analysts connect multiple slicers to multiple pivot tables to create excellent reports. Slicers are of great help to users who are not familiar with your worksheet. Moreover, they take your workbook to the next level by making your workbook's impressive look and functioning.
42. Power Pivots
Power pivot is one essential excel add-in tool that you can use to perform decisive data analysis actions. The tool enables analysts to manipulate data input and create data models and group tables related to each other. Besides that, power pivot gives you more options for creating presentations from data volumes from different places. It is available in excel 2019, 2016, 2013, 2010, and also in excel office 365.
43. Index Match
Index match performs so many incredible tasks in excel. It is the combination of two powerful functions in excel, index, and match. Index returns the value of a cell in a table basing on the column and row number, whereas match returns the position of a cell in a row or column. This function offers excellent flexibility in data analysis once a user understands how it works.
44. Indirect
The Microsoft excel indirect function returns a reference to change. An analyst can use indirect reference to create a reference specified by a certain text from the cell values. Also, it helps lock the specified cell in a formula, so it does not perform calculations. Indirect references are not affected even after changing or deleting a cell reference.
45. Iferror
Data analysts use the Microsoft excel iferror function to trap and handle errors in a given formula. The function works by returning an alternate value if a formula results in an error. It checks for errors such as #N/A, #VALUE!, #REF!, #NAME, and #NULL. Furthermore, the built-in excel function can be a worksheet function by entering a formula in a worksheet.
46. Xlookup
The MX lookup is a great search tool in Microsoft Excel that allows you to find specific values from various cells. It acts as a modification of the standard lookup functions such as vlookup. Xlookup is the newest model of vlookup with most limitations eliminated. Data analysts use this powerful tool to search for specific entries from an extensive array of data. Xlookup increases speed and productivity.
47. Find/Search
The Microsoft excel search function returns the location of one text string inside another. The search usually allows wildcards and is not always case-sensitive. Find function is case-sensitive and is used to return the position of a specific cell within a text.
48. Drop Down List
The dropdown list is an excel function for data validation that allows its users to select an option from any list of choices. In data analysis, it helps incorporate scenarios and make a worksheet dynamic. Managers and business owners use dropdown lists to ensure that users select an item from the list instead of typing their value. The skill guarantees accuracy and impactful information. It also speeds up the data entry process, especially if the entries are long.
49. Formulas
Formulas are expressions that calculate the value of a given range of cells in excel. There are so many advanced excel formulas that every experienced financial analyst should know. The formulas range from simple primary mathematics to very complex statistical, logical, and engineering tasks like IF functions. Compiled below are the top 10 most useful excel formulas.
- Sum, Count, Average
- IF Statements
- Sumif, Countif, Average
- Vlookup
- Concatenate
- Max $ Min
- Conditional formatting
- Index + Match
Mastering these basic excel formulas is vital for any beginner who is working towards becoming a proficient analyst.
Excel is probably the most convenient and commonly used spreadsheet for data analysis in many corporations, firms, and businesses. Even with larger data sets, Excel makes it easy for users to study and visualize the information more accurately. Learning and using the above excel skills correctly is the key to success for your profession as a data analyst.
50. IF Formulas
There are several formulas in excel, and this can become overwhelming when you are getting started with data analysis. The IF Formula is used in Microsoft Excel to run logical tests, returning one value for an actual result and another for a false result. IF functions test more than one condition, which is helpful if you want to check something on a worksheet. Suppose functions help data analysts to automate decision-making in their spreadsheets.