In the modern workplace, the importance of Excel skills can’t be understated, particularly in improving productivity and data crunching capability. This blog will guide you through 20 Excel skills that should be learned by professionals in 2024 to maximise their potential in Excel. From core aspects of using Excel to the more advanced features within the suite of tools, our goal is to aid users in utilising Excel to its highest potential. If you’re ready to take your Excel usage to the next level, read on!

1. Basic Navigation and Interface in Excel

Understanding the Excel Interface

The first level is simply to be familiar with the Excel interface – to know where the menus, toolbars and options are. Most of Excel’s tools are kept on the ribbon and this can also be customised, so you can keep the functions you use most often front and centre.

Customizing the Ribbon and Toolbars

Adjusting the ribbon and toolbars to your liking by adding, removing or rearranging tabs and groups allows you to create a more efficient workflow by saving you time and allowing you to access the tools you need most easily.

Navigating Through Worksheets and Workbooks

Productive people know how to make their way through worksheets and workbooks, from easily switching between tabs with keyboard shortcuts to mastering how to navigate multiple sheets in a single workbook. It’s vital to be nimble with these documents, particularly given the amount of data and complexity many projects can involve.

2. Data Entry and Formatting

Efficient Data Entry Techniques

If time can be saved by entering data correctly, it shouldn’t be wasted. For example, by using features such as AutoFill, Flash Fill and data validation, repetitive data entry tasks can be automated. This will also reduce the chances of entry errors, and save valuable time.

Using Cell Formatting Options

Formatting of cells improves the readability and appearance of your data. Use different font, colour and border formats to visualise key elements. Conditional formatting is particularly useful here. For example, you can automatically change the format of your cells according to saving data, for example. Thus, making the pattern and the trends visible from the conditional formatting.

Applying Conditional Formatting

Visual data analysis just got more powerful with the new conditional formatting feature. Now when you need to view and analyse data, you’ll be able to highlight the data points you need, using your criteria (for example, cell values or formulas) to pinpoint the outliers, trends and key metrics you’re looking for. 

3. Formulas and Functions

Basic Formulas (SUM, AVERAGE, COUNT)

SUM, AVERAGE and COUNT are the most basic formulas you can write in Excel. Data manipulation starts with adding up values (SUM), calculating the mean (AVERAGE) and simply counting the number of items (COUNT) that fill a range. If you can’t execute these functions, you won’t be able to do many other things quickly.

Common Functions (IF, VLOOKUP, HLOOKUP)

Other more complex data analysis functions such as IF, VLOOKUP and HLOOKUP are also useful but crucial to effective data analysis. IF allows you to use logical conditional sentences, such as “if the price is more than $500, return some value”. The VLOOKUP enables you to search for an input value in a table, while HLOOKUP does the same horizontally. As your analytical needs scale up, these extra functions will help you to efficiently gather and extract data from larger sets.

Using Nested Functions

The secret to constructing these complex formula lies in using nested functions, i.e., combining more than one function within a single calculation. (If does, in fact, nest with VLOOKUP.) Nesting increases the power and flexibility of your Excel analytics.

4. Data Sorting and Filtering

Sorting Data in Ascending/Descending Order

By sorting your data, you are simply logically organising the information. If you need to see what the highest or lowest values are for a column of information, you may want to sort the information from highest to lowest or from lowest to highest in ascending or descending order.

Using Filters to Find Specific Data

The filters are used so that you can further filter the data, for example, if you are only interested in January’s entry you can choose that month. By pointing at one of the column headers, another drop-down will show up to set your criteria for the filter. For instance, if you are interested only in the German value for January, you can set that for your filter and as soon as you hit enter, only a single entry will be displayed. Filters are useful if you have a large dataset.

Advanced Filtering Techniques

Complex filtering techniques involve multiple criteria and conditions. Excel’s advanced filter options offer even more sophisticated data filtering and retrieval mechanisms such as searching for dates in a given period, or text patterns. These techniques are useful for in-depth data analysis.

5. Pivot Tables

Creating and Customizing Pivot Tables

A pivot table is a popular tool to summarise and analyse data in a large data set. You can reorganise and group data by dragging fields from an Excel worksheet to four types of cells RC, first row, R, and C. In turn, the four types of cells (row labels, column labels, result labels and values) have different logic.

Analyzing Large Datasets with Pivot Tables

The ease with which you can quickly calculate sums, averages, counts and other summary statistics, apply filters and range-selectors to panic-free data, and drill down by group (for example) into a bar chart, pivot table, etc – that’s what makes it possible for managers and investors to analyse any nth degree of any given dataset and, with a little practice and intuition, decide whether it’s worth an action.

Using Pivot Charts

Pivot charts work into the side of a pivot table, showing the data graphically and making it easier to detect trends and patterns in the data. You can customise your pivot charts so that the important key insights and patterns jump off the page and make your data more impactful.

6. Charts and Graphs

Creating Different Types of Charts

There are different types of charts in Excel, for example, bar charts, line charts and pie charts. Bar charts are useful for comparing several products sold across categories. Line charts show trends over time. The pie chart shows the proportion of different types of products purchased. So, it is important to know which type of chart to use for given information and how to use it.

Customizing Chart Elements

Having customised charts can improve readability and therefore improve impact. By altering the colour of lines or bars; the label descriptions; the legend boxes and footers; and the titles of the chart, you can make sure that they match your overall presentation style, and can identify key points of the data more successfully.

Using Sparklines for Data Visualization

Sparklines are small charts inside a cell of a table that can quickly show the trend of your data. They are great when you are looking to show a trend in a series of points while keeping your cell compact. Because of their compact size, use sparklines as quick visual insights directly in the same table with your data.

7. Data Validation

Setting Data Validation Rules

Data validations can be used to validate the data in the cells, to protect your data against invalid data entries. Data validation helps to limit the type of data that you can allow in the data cells. You can establish the data type that the cell should contain, or you can restrict the entry to variables within any range or to a specific list of values.

Creating Drop-Down Lists

Drop-down boxes are one form of data validation that shows the user all possible options without them needing to remember or type in the value and allowing only this list of options to be chosen from. This makes data entry much easier and avoids errors when input data such as name, state, country, or colour is entered since it limits the choices the user can input. A drop-down box is just one step towards making your spreadsheets much more user-friendly.

Preventing Data Entry Errors

Data validation helps to reduce data-entry errors by setting up rules to accept or reject data with drop-down lists that make it difficult for an error to creep in. In this way, one can be fairly certain that no incorrect data creeps into your analysis.

8. Conditional Formatting

Applying Basic Conditional Formatting Rules

Conditional formatting will apply formatting to the cells based on certain conditions, such as if the value is above or below a value, or contains specific text. This allows the quick identification of important cells.

Using Formulas for Conditional Formatting

Some of the rules available with this level of conditional formatting are more complex than others, and increasing the complexity becomes easier with the ability to use a formula to perform the check. For example, you can highlight cells based on whether or not the output of a formula based on several other cells meets a certain value. Having this level of sophistication makes conditional formatting an immensely powerful tool with which to interrogate your data.

Managing Conditional Formatting Rules

Conditional formatting rules need to be created and perhaps edited or removed as and when you need to change your rules. Excel also has a rules manager to help you see a list of rules applied and ensure they’re in priority order so your formatting applies correctly.

9. Workbook Management

Organizing Multiple Worksheets

If you’re working on more than one sheet’s worth of information, it’s best to put multiple worksheets into the same workbook. Make sure that every worksheet has the same kind of name, and that they are sorted the same way. Try to avoid going back and forth between worksheets if you want to stay productive. Think about adding colour to your tab titles to help you distinguish the kind of data at a glance if there’s more than one.

Using Templates for Efficiency

Templates serve an invaluable purpose of serving up ready-to-use blueprints for specific tasks, including the layout of cells and formatting of information, such as tables and charts for a memo. Excel ships with a collection of built-in templates for common tasks (eg, scheduling a meeting, writing a sales quiötation, tracking a proposal), and you can create your recurring tasks to save the same setup time.

Protecting and Securing Workbooks

When you protect your workbooks by specifying which worksheets can be accessed, and which cells or sheets can be edited in a workbook, you regret only that you weren’t doing this all along. Threading, in some vague sense, is the path along which information is carried, from one place to another in your brain. 

Trellis, in some equally blurry way, is a structure comprising threads. In any to-do list app, the trails represented by task lists exist within the central brain that is the app itself. But sometimes, tasks are linked, and to grasp that linkage, and maintain it as you work, you might be using a hybrid analogue app, half-brain and half-calendar, such as a notebook or journal. 

Protecting your workbooks and worksheets from unauthorized modifications is paramount. So are locking down specific cells and entire sheets to prevent inadvertent or malicious alterations, and password-protecting your workbooks to control who sees what and how. Protecting your workbooks and worksheets helps maintain data integrity and ensure confidentiality.

10. Text Functions

Using Text Functions (LEFT, RIGHT, MID)

Text functions allow you to extract parts of strings. For example, LEFT returns the left part of a text string, RIGHT returns the right side of a text string, and MID returns the middle characters of a given text string. For example, the following equation transforms a text string: =RIGHT(A2,11)

Concatenation and Text Splitting

Concatenation: Joining multiple text strings into a single string with either the CONCATENATE function or the ‘&’ operator. 

Text splitting: Taking a single text string and dividing it into multiple parts with functions such as LEFT, RIGHT MID or the Text to Columns tool.

Cleaning and Preparing Text Data

Text data is characterised by an extensive amount of spaces and formatting added by the writer; the first step in prepping text data is therefore to normalise its output by removing redundant characters, spaces and formatting. Functions such as TRIM, CLEAN and SUBSTITUTE are crucial to this process. Standardising elements like currencies, weights, dates, addresses and phone numbers allows for more consistent and reliable analysis and reporting.

11. Date and Time Functions

Calculating Dates and Times

When you’re working with dates and times, Excel’s date and time functions can crunch the heavy lifting: DATE calculates the date of an event given a start day and year; WEEKDAY calculates the day of the week of an event given a date; TIME returns the time of day for an event given the hour; NOW returns the current system date for a value; and dozens of others can add and subtract from dates, find the difference between two dates, and schedule deadlines and milestones. When it comes to project management, planning, and tracking schedules, these functions are invaluable.

Using Functions Like TODAY, NOW, and DATE

Functions such as TODAY and NOW return the current date and date-time respectively; and DATE accepts separate year, month and day inputs to make a new date. Dynamic reports and time-dependent calculations can be achieved through these functions, which update automatically to the current moment in time.

Formatting Dates and Times

Formatting date and time in Excel makes your dashboard cleaner and easier to interpret. Excel has different date and time format options like short date, long date, custom format etc. You can format your date and time as you want so that the data is not just aesthetically pleasing but also makes interpretation easier.

12. Lookup and Reference Functions

Using VLOOKUP and HLOOKUP

We often use VLOOKUP (vertical) and HLOOKUP (horizontal ) functions on large tables to find data and sort information when dealing with big amounts of data. The cell address of sorted data is found in a specified column or row from the given table or chart, which is more convenient.

Understanding INDEX and MATCH

INDEX and MATCH are more flexible versions of VLOOKUP and HLOOKUP. Use INDEX to retrieve a value from a table, based on references to the row or column. Use MATCH to retrieve the location of a value within a range. Together, they enable powerful and flexible data retrieval.

Using XLOOKUP for Advanced Searches

Replacing VLOOKUP and HLOOKUP is the more powerful and flexible function XLOOKUP, which looks up a value in a list or across a table and finds the corresponding value in that same direction, including up, down, left, and right. You can also get fancier with your search criteria and fallback options with XLOOKUP. It also offers better error handling and matching options. 

13. Financial Functions

Understanding Basic Financial Functions (PMT, FV, NPV)

For finance, the basic financial functions are PMT, FV and NPV. Their names are loan payment function, future value function and net present value function for cash flows. Suppose that you want to know how much you should pay every month to the bank if you borrow $250,000 from the bank. PMT function can quickly solve it. Suppose you want to know how much amount pension fund will turn into in 10 years if you invest $515,000 now. 

FV function can be used for it. Suppose you want to calculate how much amount is the net present value of cash flows for a five-year investment project. NPV function can be used for it. All these functions can be used when making a financial plan, budget and investment analysis.

Using Functions for Financial Analysis

For more serious financial analysis, you can use Excel’s inbuilt functions such as IRR (internal rate of return) and RATE (rate of interest) and calculate a return on an investment or estimate the interest rate for an investment.

Creating Financial Models

Financial modelling requires many financial functions and various data inputs. Excel’s flexibility helps you to build detailed financial models for your financial forecasts, scenario analyses and decision-making strategies. Thus, the mastery of financial functions is vital for producing efficient and reliable financial modelling.

14. Logical Functions

Using Logical Functions (IF, AND, OR)

Logical functions – if, and, or – are essential for conditional expressions that result in a true or false answer if a logical test is passed. If returns one value if true and another if false; and and or functions test more than one condition, making it easier for users to produce compound logical expressions.

Combining Logical Functions for Complex Criteria

Then you can combine logical functions to create more sophisticated reasoning. For example, you can put IF together with AND or OR to test a set of conditions within a single formula. You wouldn’t want to have a limited range of reasoning capacities in your device. How would we deal with conditional calculations and decision rules?

Applying Logical Tests in Data Analysis

For example, a logical test will find cells that meet certain criteria, or provide a total of subsets of data. Logical functions help you do detailed and conditional data analysis.

15. Statistical Functions

Using Basic Statistical Functions (AVERAGE, MEDIAN, MODE)

Data distributions can be summarised with simple statistical functions, such as AVERAGE, MEDIAN and MODE. The average of a data set is the mean of its values. The median is the middle value, and the mode is the most frequent value. These three functions will give you an instant idea of what is typical for your data.

Applying Advanced Statistical Functions (STDEV, VAR)

Sophisticated descriptive statistic functions such as STDEV (standard deviation) and VAR (variance) quantify data dispersion, allowing you to check the variability and consistency of data, which is especially important for statistical evaluation and quality control.

Analyzing Data Distributions

You use basic and advanced statistical functions to gain insight into the characteristics of distributions of data. Histograms, quartiles and percentiles are tools to analyse and interpret these data distributions. A lot of work in analysing data involves using and understanding these functions. 

16. Error Checking and Troubleshooting

Identifying Common Excel Errors (#VALUE!, #REF!, #DIV/0!)

Simple errors such as #VALUE!, #REF! and #DIV/0! that crop up in Excel are used to describe specific problems in your formulas. #VALUE! is raised when there’s a data-type issue, #REF! means you have an invalid cell reference, and #DIV/0! is raised when a formula is divided by zero. Being able to recognise these faults will help you to identify them immediately and correct them.

Using Error-Checking Tools

Excel comes with error-checking features to help you identify and correct errors. The Error Checking option that you’ll find on the Formulas tab can identify errors in your formulas and, in many cases, suggest ways to fix them. Whenever possible, make use of these tools so your data and calculations will be correct.

Troubleshooting and Resolving Formula Issues

Troubleshooting formula errors takes you back to the origin In addition to the little Yeti arrow, Excel offers a more detailed analysis to help you correct the formula that’s giving you fits. Select the cell that contains the error, and then select the Formula tab at the top of the screen. You’ll see a button reading Evaluate Formula. The result reveals the answer you might have calculated or, in the case of the False entry, the illogical result. From there, select the Trace Error option and the program will walk you through the chain of events that led to your headache. Excel’s diagnostic tools can help you isolate the offending formula, so you can see where things went awry and either correct the offending formula or rework the logic.

17. Macros and Automation

Recording and Running Macros

Macros automate tasks that you do over and over again in Excel. You instruct Excel to record a series of steps, like changing cell formats in a report or copying data cells across a large spreadsheet, and then storing it as a macro. Later, you can have Excel run the macro, and it will do the task for you.

Editing Macro Code in VBA

You can do fancier automation by tweaking the recorded macros or writing new ones from scratch with VBA (Visual Basic for Applications) code. It’s key to know how to do this, as it’ll help you automate more sophisticated workflows and optimise tasks.

Automating Repetitive Tasks

We could also parse incoming requests and use that information to look for and update other data fields and just do a lot of busy work that can be automated with macros or VBA. Often, the trick is to have software do menial tasks that would take you forever to do and for which you would only have a one-off need. If you do it yourself, the minutes you spend will accrue. If automation takes care of it, those minutes are freed up to do something more important. This means that workers are being more productive.

18. Collaboration and Sharing

Sharing Workbooks with Others

Workbooks can be shared between users. In Excel you have the option to email, add to cloud storage, or put into a network drive file, you can choose who to share with and at what level, for example: view-only access or edit access, to secure data while collaborating.

Using Excel Online for Real-Time Collaboration

The desktop software Excel is enhanced with its online version allowing real-time collaborative work by multiple users simultaneously on a single document. As all changes are immediately visible to all the collaborators, it became possible to leverage improved cooperation within teams and spend much less time on version control.

Tracking Changes and Comments

Tracking any changes before sending Excel worksheets around and creating comments enables you to cooperate. Excel has a useful function, ‘track changes’, which notes every modification. It becomes easier to review and give your consent to those, which are a part of the completion. You can create a comment for every cell inside the workbook directly, and it will therefore be easy to discuss with your team members.

19. Power Query

Importing Data from Various Sources

You can use Machine Learning to import data from a wide array of sources, including databases, web pages, and Excel files. You can also use Power Query to consolidate data from a multitude of sources into a single workbook, saving you time that you would otherwise spend on the drudgery of hand-cutting and pasting.

Transforming and Cleaning Data

Power Query gives you all kinds of tools for transforming your data and cleaning it up: you can remove duplicates, filter out rows, change the data type, merge tables, and so on. All of this allows you to clean up your data ready for use.

Using Power Query for Advanced Data Manipulation

These development storyboards could do things such as Create custom columns. Perform pivoting. Join (or Union or Append) queries together. This is about as advanced as it gets in terms of data manipulation features with Power Query. At this point, who needs to go to Microsoft Access to do data transformation? Who needs to do ETL (extract, transform and load) operations for data warehousing, when we can do it at our desk with data we can trust? Instead of getting bogged down with tedious manipulations, you will be able to quickly churn out more refined charts and graphs for your dashboard.

20. Power Pivot

Creating Data Models with Power Pivot

After importing data into Power Pivot, you can combine it, make calculations, define relationships between tables, and create measures. This gives you the ability to combine completely different data sets from different sources, and run queries across millions or even billions of rows.

Using DAX Functions for Calculations

There are several powerful calculation functions available in Power Pivot that are available through the Data Analysis Expressions (DAX). DAX expressions are the script language used to create custom calculations and aggregations within the data model. Learning DAX is an important part of learning how to perform detailed and complex data analysis.

Analyzing Data with Power Pivot Tables

Power Pivot tables are great for slicing-dicing information into summary data and creating reports. They enable analysis on multiple dimensions and are critical analysis tools for advanced data analysis and business intelligence. They reinforce and extend the native capabilities of Excel’s pivot tables. 

Conclusion

You will excel in your job and be productive if you can master any or all these 20 Excel skills by practising daily for a couple of months. Employers are looking for candidates who can analyse data better and do so more quickly. I encourage you to pick one or two skills and start practising today. If you find a few of these skills challenging, don’t worry. It takes time to excel in all areas. Feel free to learn from my errors and reach out to me if you need additional help and resources. You can start exploring courses and resources that can help you develop your Excel skills. This way, you can always stay a step ahead in this busy, data-driven workplace. Happy learning, and stay curious.