15 Essential Excel Skills to Learn for the Workplace

Microsoft Excel is used for maintaining business records, preparing reports, calculating figures and organising information. However, knowing how to enter data and use a few formulas is only the starting point. Many workplace tasks require a better understanding of Excel functions, data organisation and reporting features.
For example, an administrative employee may use Excel to maintain attendance records, while someone in finance may calculate monthly expenses, compare budgets and prepare financial summaries. Both may use the same software, but the Excel skills required for their work can be different.
Learning Excel from basic to advanced level helps you handle these tasks with greater accuracy. This guide covers 15 essential Excel skills for the workplace, explaining what each skill does, where it is used and which features you should learn. If you want to develop these skills through guided training, explore the Microsoft Master Excel Course in Singapore, which covers Excel fundamentals, formulas, data analysis and advanced workbook features.

1. Basic Excel Skills: Building a Strong Foundation

Before working with complex formulas or large datasets, you need to understand how Excel handles workbooks, worksheets, cells and information. These first four skills help you create spreadsheets, enter information correctly and perform basic calculations.

1. Understanding the Excel Interface and Navigation

The first step in learning Microsoft Excel is understanding its interface. Excel contains several sections that help you enter information, edit data and access different commands.
The main parts include:

Ribbon: Contains tabs such as Home, Insert, Formulas, Data and Review. Each tab provides commands for specific tasks.
Formula Bar: Displays the content or formula entered in the selected cell. You can also edit a cell’s contents here.
Name Box: Shows the address of the selected cell, such as A1 or D15. You can enter a cell reference to move directly to it.
Worksheet Area: The main working space containing rows, columns and cells.
Sheet Tabs: Allow you to switch between worksheets within a workbook.
Quick Access Toolbar: Provides shortcuts to frequently used commands, such as Save, Undo and Redo.
Workplace example: Suppose you receive an Excel file containing 500 employee records. Instead of scrolling through the entire sheet, you can type D350 into the Name Box to move directly to that cell. The Formula Bar can then help you check or edit its contents.
What to learn: Ribbon navigation, selecting cells and ranges, using the Name Box, editing cell contents, and switching between worksheets.

2. Creating and Managing Excel Workbooks and Worksheets

An Excel workbook is the complete file, while worksheets are the individual tabs inside it. Understanding how to manage both is important when working with information for different departments, months or projects.

You should learn how to:

Create a new workbook and save it in the correct file format.
Add, rename, copy, move and delete worksheets.
Hide and unhide worksheets when certain information does not need to remain visible.
Move between multiple worksheets.
Freeze rows or columns to keep headings visible while scrolling.
Organise related information across different sheets.

Workplace example: An HR employee may maintain one workbook for employee records.

The workbook could contain separate worksheets for employee details, monthly attendance and leave records. Each sheet keeps its information separate while allowing the employee to manage everything in one file.
The Freeze Panes feature is also useful when a worksheet contains hundreds of rows. It keeps the heading row visible while you scroll through employee or customer records.
What to learn: Workbook creation, worksheet management, file saving, Freeze Panes, and working with multiple sheets.

3. Entering and Formatting Data Correctly

Data entry is one of the most common activities in Excel. However, entering information in the wrong format can cause problems when you apply formulas, sort records or perform calculations.

Excel handles different types of information, including:

Text: Employee names, product names and department details.
Numbers: Sales figures, quantities and employee IDs.
Dates: Joining dates, invoice dates and payment deadlines.
Percentages: Tax rates, discounts and sales growth.
Currency: Salaries, expenses and transaction amounts.

You should also understand how to format this information so that it is easier to read.

Important formatting features include:

Changing font style, size and colour.
Adjusting row height and column width.
Applying borders and cell alignment.
Using number, date, percentage and currency formats.
Applying Wrap Text when a cell contains longer information.
Using AutoFill to extend dates, numbers and other repeating patterns.

Workplace example: Consider a monthly expense sheet containing expense categories, transaction dates and amounts. Applying a date format to the transaction column and a currency format to the amount column makes the information easier to review.

One important point: changing a cell’s number format usually changes how the value appears, not the underlying value stored in Excel. For example, displaying 1500 as $1,500.00 does not change the original number.

What to learn: Data types, number formatting, cell alignment, AutoFill, Wrap Text and basic spreadsheet presentation.

4. Using Basic Excel Formulas and Cell References

Formulas allow you to perform calculations without working out every figure manually. Excel formulas usually begin with an equal sign (=), followed by a function, cell reference or calculation.
Start with these commonly used Excel formulas and functions:

Formula	Purpose	Example
SUM	Adds numbers	=SUM(B2:B10)
AVERAGE	Calculates the average	=AVERAGE(B2:B10)
MIN	Finds the smallest value	=MIN(B2:B10)
MAX	Finds the largest value	=MAX(B2:B10)
COUNT	Counts cells containing numbers	=COUNT(B2:B10)

Along with these functions, learn how cell references work. They determine which cells a formula uses when you copy it to another location.

Relative reference (A1): Changes when a formula is copied to another cell.
Absolute reference (A1): Keeps both the row and column fixed.
Mixed reference (A1orA1): Keeps either the column or row fixed.
Workplace example: Suppose you maintain a monthly sales sheet where product quantities are recorded in column A and prices in column B. You can calculate the total value of each product using a multiplication formula. At the bottom of the sheet, SUM can calculate the overall sales amount.Understanding cell references becomes important when the same calculation needs to be applied across multiple rows or columns.
What to learn: Basic arithmetic operators, SUM, AVERAGE, MIN, MAX, COUNT, and relative, absolute and mixed cell references.

If you are starting from the beginning, you can explore this guide to Excel basics for beginners, including workbook management, cell formatting and essential formulas. These skills will help you build the foundation needed for more advanced tasks.

2. Intermediate Excel Skills: Managing and Analysing Workplace Data

Once you are comfortable creating spreadsheets and using basic formulas, you will start coming across tasks that require more than simple calculations. You may need to check whether employees meet certain requirements, calculate sales for a particular region, find customer details from a large list or identify records that need attention.
These tasks require a better understanding of intermediate Excel skills. The following five skills explain how to handle such situations and which Excel features can help you complete the work.

5. Using IF and Logical Functions to Check Conditions

In many workplace spreadsheets, a calculation depends on whether certain conditions are met. For example, an employee may qualify for an incentive only after achieving a sales target, or an invoice may need to be marked as overdue when its payment date has passed.
The IF function in Excel helps you handle these situations by checking a condition and returning one result when it is true and another when it is false.

Its basic syntax is:
=IF(logical_test, value_if_true, value_if_false)
Consider an employee performance sheet containing employee names, monthly sales and sales targets. If actual sales are recorded in column B and the target is in column C, you can use:
=IF(B2>=C2,”Target Achieved”,”Target Not Achieved”)
Excel compares the employee’s sales with the target and displays the appropriate result. You can copy the formula down to check the remaining employees.

Where do logical functions become useful?

A single IF condition may be enough for a simple check. However, some workplace decisions depend on two or more conditions. Excel provides additional logical functions for these situations.

AND function: Checks whether all specified conditions are true. For example, an employee must meet the sales target and maintain at least 90% attendance to qualify for an incentive.
OR function: Returns TRUE when at least one condition is met. For example, a customer may receive a discount if they are a premium member or have placed an order above a specified amount.
NOT function: Reverses a logical result. It can help identify records that do not meet a particular condition.
Nested IF: Allows you to check multiple conditions and return different results for different situations.
For example, a sales report may classify performance into three categories:

=IF(B2>=100000,”High”,IF(B2>=50000,”Medium”,”Low”))
Here, sales of 100,000 or more are classified as High, sales of 50,000 or more as Medium, and the remaining values as Low.
The important part is understanding the order of conditions. If the conditions are arranged incorrectly, Excel may return a result that does not match the intended classification.
What to practise: Create an employee performance sheet, apply IF to check targets, combine IF with AND or OR, and use nested IF to assign multiple performance categories. This will help you understand how logical functions in Excel are used in everyday reporting.

6. Calculating Data Based on Specific Conditions

A sales or expense report may contain hundreds of transactions, but you may only need the total for one product, department or month. Calculating the entire column and checking individual entries manually can become time-consuming.
This is where conditional Excel functions help. They perform calculations only on records that meet the conditions you specify.
Consider a sales register containing four columns:

Date	Salesperson	Region	Sales Amount
05-Jan	Daniel	North	12,000
08-Jan	Chloe	South	18,000
12-Jan	Ethan	North	15,000
15-Jan	Grace	East	10,000
19-Jan	Daniel	North	20,000

Suppose a manager wants to know how much sales revenue came from the North region. Instead of filtering the records and adding the amounts manually, you can use:
=SUMIF(C2:C6,”North”,D2:D6)
The formula checks the Region column and adds only the sales amounts associated with North. The result is 47,000.

Which conditional functions should you learn?

Excel function	How it works	Example workplace task
SUMIF	Adds values that meet one condition	Calculate total sales for one region
SUMIFS	Adds values that meet multiple conditions	Calculate sales for a region during a selected month
COUNTIF	Counts cells that meet one condition	Count employees in a department
COUNTIFS	Counts records matching multiple conditions	Count employees in a department with a particular attendance status
AVERAGEIF	Calculates an average for matching records	Find average sales for one product
AVERAGEIFS	Calculates an average using multiple conditions	Find average sales for a region during a selected period

For example, if a manager wants to calculate sales from the North region for a particular salesperson, SUMIFS can check both conditions:
=SUMIFS(D2:D6,C2:C6,”North”,B2:B6,”Amit”)
This formula checks the Region and Salesperson columns before adding the matching sales amounts.

Why learn these functions?

These formulas are useful when reports need to be updated regularly. You can change the criteria without rebuilding the calculation each time. They also help when a manager asks different questions about the same sales, expense, attendance or inventory records.
What to practise: Use SUMIF and SUMIFS to calculate sales totals, COUNTIF and COUNTIFS to count matching records, and AVERAGEIF to compare average values. Try changing the criteria to see how the results change.

7. Finding Related Information with VLOOKUP, HLOOKUP and XLOOKUP

Imagine that you have an employee attendance sheet containing employee IDs, but you also need to display each employee’s name and department. These details are already available in another worksheet.
You could search for every ID manually and copy the corresponding details. However, this becomes difficult when the employee list contains hundreds of records.
Excel lookup functions help retrieve related information by matching a value in one list with a value in another.
The three functions worth learning are VLOOKUP, HLOOKUP and XLOOKUP.

VLOOKUP: Finding information in a vertical table
VLOOKUP searches for a value in the first column of a selected table and returns information from another column in the same row.
For example, consider an employee database with Employee ID in column A, Name in column B and Department in column C. If you enter an employee ID in E2, you can retrieve the employee’s name using:
=VLOOKUP(E2,A2:C100,2,FALSE)
The formula searches for the ID entered in E2, checks the first column of the selected range and returns the corresponding name from the second column. The FALSE argument tells Excel to look for an exact match.

HLOOKUP: Finding information in a horizontal table
HLOOKUP works in a similar way, but it searches across the first row of a table instead of the first column.
For example, if monthly sales figures are arranged horizontally, with month names in the first row and sales figures in the rows below, HLOOKUP can retrieve the figure associated with a selected month.
XLOOKUP: A more flexible way to find records
XLOOKUP can search a range and return a matching result from another range. It can look to the left or right of the search range and allows you to specify what should appear when no match is found.
For example:
=XLOOKUP(E2,A2:A100,B2:B100,”Not Found”)
Here, Excel searches for the employee ID in E2 within A2 and returns the corresponding name from B2. If there is no matching ID, it displays “Not Found”.

What should you understand before using lookup functions?

The lookup value must match the value stored in the source list.
Extra spaces or differences in data formats can prevent a match.
VLOOKUP searches in the first column of its selected table range.
The column number in VLOOKUP must point to the correct return column.
XLOOKUP allows separate lookup and return ranges, which makes it more flexible.

Workplace example: An accounts employee may receive a payment register containing invoice numbers and payment amounts. A separate invoice list contains customer names and invoice dates. A lookup formula can retrieve the required customer details using the invoice number, without manually searching both sheets.
Learning these functions is an important part of Excel data retrieval and lookup techniques, especially when information is maintained across multiple lists or worksheets.

8. Highlighting Important Information with Conditional Formatting and Data Validation

A spreadsheet may contain all the required information but still be difficult to review. For example, an accounts team may maintain hundreds of invoice records, while an HR team may update employee attendance every day.
Two issues can arise in these sheets: important records may be difficult to identify, and different employees may enter information in different formats.
Excel provides two features to handle these situations: Conditional Formatting and Data Validation

Conditional Formatting: Highlighting records that meet a rule
Conditional Formatting changes the appearance of cells when they meet a condition. You do not need to check every value individually because Excel highlights the records based on the rules you set.
Common uses include:

Highlighting overdue invoices based on their due dates.
Identifying duplicate employee IDs or invoice numbers.
Marking sales figures below a monthly target.
Comparing expenses using colour scales.
Highlighting values above or below a specified limit.
Displaying data bars to compare sales or performance figures.

For example, an accounts employee can select the invoice due-date column and apply a rule to highlight dates earlier than today’s date. This helps identify invoices that may require follow-up.
You can access these options through Home → Conditional Formatting. Excel also allows you to create custom rules using formulas when the condition depends on information in another column.

Data Validation: Controlling what users can enter
Data Validation helps prevent incorrect or unwanted entries by setting rules for selected cells.
For example, an attendance sheet may require employees to select one of three options: Present, Absent or On Leave. Instead of allowing everyone to type their own version, you can create a drop-down list containing these approved entries.
Other uses include:

Restricting entries to whole numbers within a specific range.

Allowing dates only within a selected period.
Setting limits on the length of text.
Creating drop-down lists for departments, locations or payment statuses.
Displaying an error message when someone enters an invalid value.
You can set these rules through Data → Data Validation.

Workplace example: Consider a monthly attendance register used by several HR employees. Data Validation can provide the same attendance options for every record, while Conditional Formatting can highlight absent employees. Together, these features make the sheet easier to update and review.
What to practise: Create drop-down lists, set date and number restrictions, highlight duplicate values, apply colour scales and create rules based on cell values. These exercises will help you understand how Excel Conditional Formatting and Data Validation work in real spreadsheets.

9. Organising Records with Excel Tables, Sorting and Filtering

As a spreadsheet grows, adding new records and managing existing information can become difficult. For example, a customer database may start with 50 entries but gradually expand to several hundred. If the information is not organised properly, finding a particular customer or updating the list can take longer.
Excel Tables help organise related information into a defined range with built-in features for sorting, filtering and managing records.
To create a table, select the data range and press Ctrl + T. Make sure the option indicating that your data has headers is selected if the first row contains column names.
Once the range is converted into a table, you can use several features:

1. Sorting records

Sorting arranges information in a selected order. You can sort customer names alphabetically, dates from oldest to newest, or sales amounts from highest to lowest.

2. Filtering information

Filters display only the records that match your selection. For example, a manager can filter a sales register to view transactions from one region or a customer list to display only active accounts.

3. Adding new records

When you enter information in the row immediately below a table, Excel can expand the table to include the new record. This helps keep new entries within the same data range.

4. Using calculated columns

If you add a formula to a table column, Excel can apply that formula to the other rows in the column. For example, a sales table can calculate total amounts using quantity and unit price.

5. Working with structured references

Instead of referring to cells using addresses such as D2, Excel Tables allow formulas to refer to table and column names. This can make formulas easier to understand when working with larger datasets.

Workplace example: Suppose a company maintains an order register with order IDs, customer names, order dates, regions, quantities and payment status. By converting this data into an Excel table, the sales team can filter pending payments, sort orders by date and add new transactions without repeatedly adjusting the original range.
One important point: tables work best when the source data has clear column headings, one type of information per column and no unnecessary blank rows within the dataset. Merged cells and inconsistent headings can create problems when sorting, filtering or using formulas.
What to practise: Convert a normal data range into an Excel Table, sort records by date and amount, apply multiple filters, add calculated columns and use structured references in formulas.

3. Advanced Excel Skills: Working with Complex Data and Reports

Once you are comfortable with formulas, lookup functions and data organisation, you can move towards advanced Excel features. These skills are useful when you need to summarise large datasets, manage information across multiple worksheets, check calculations and handle repetitive tasks.

10. Creating PivotTables for Data Analysis

When a sales or expense sheet contains hundreds of records, reviewing each row individually can make it difficult to understand the overall results. Excel PivotTables help summarise this information by categories such as product, region, employee or month.
For example, a sales manager can use a PivotTable to:

Calculate total sales for each region.
Compare monthly sales figures.
Identify top-selling products.
Review individual employee performance.

To create one, select your dataset and go to Insert → PivotTable. Add the relevant fields to Rows, Columns, Values and Filters to organise the report.
You can also use PivotCharts to present the results visually and Slicers to filter information through clickable options.
What to learn: PivotTables, PivotCharts, Slicers and grouping data by dates or categories.

11. Using Advanced Excel Functions

Some workplace calculations require more than basic formulas or single-condition functions. Advanced Excel formulas help retrieve information, handle errors and work with data across different ranges.
Important functions include:

INDEX and MATCH: Retrieve information based on a matching value.
IFERROR: Display an alternative result when a formula returns an error.
FILTER: Display records that meet selected conditions.
UNIQUE: Return distinct values from a list.
TEXTJOIN: Combine text from multiple cells.

Workplace example: An HR employee can use INDEX and MATCH to retrieve an employee’s department using their employee ID. IFERROR can display “Not Found” when the ID does not exist in the database.
What to learn: INDEX, MATCH, IFERROR, FILTER, UNIQUE and TEXTJOIN, along with combining functions in a single formula.

12. Working Across Multiple Worksheets and Workbooks

Businesses often maintain separate Excel files for monthly sales, employee records, expenses and departmental reports. Excel workbook management helps you bring related information together without manually copying every figure.
Some important features include:

Worksheet References: Retrieve values from another worksheet using cell references.
3D References: Calculate values across multiple worksheets using a formula such as =SUM(January:March!B2).
External References: Link information between separate Excel workbooks.
Consolidate: Combine data from different ranges into one summary.

Workplace example: A finance employee may receive monthly expense reports from three branches. Using Excel’s Consolidate feature, the employee can combine the figures into a single summary for review.
What to learn: Worksheet references, external workbook links, 3D formulas and data consolidation.

13. Checking Calculations with Formula Auditing

A workbook containing multiple formulas can become difficult to review, especially when one incorrect cell reference affects several calculations. Excel Formula Auditing helps identify where formulas get their values and which other cells depend on them.
The main tools include:

Trace Precedents: Shows which cells are used in a formula.
Trace Dependents: Identifies cells that use the selected cell’s value.
Evaluate Formula: Checks a calculation step by step.
Show Formulas: Displays formulas instead of calculated results.
Error Checking: Helps identify common formula errors.

Workplace example: If a monthly expense report shows an incorrect total, Trace Precedents can help you check which cells contribute to the calculation and locate a possible reference error.
For examples of how AI can help with spreadsheet tasks, read this guide to AI tools in Excel, which covers formula troubleshooting and data analysis.
What to learn: Trace precedents, trace dependents, and evaluate formulas and common Excel formula errors.

14. Automating Repetitive Tasks with Macros and VBA

Many employees repeat the same Excel operations every week or month, such as formatting reports, adding totals and applying filters. Excel Macros allow you to record these actions and repeat them when required.
For example, an accounts employee can record a macro that applies currency formatting, adjusts column widths and adds a total row to a monthly expense report.
Excel uses VBA (Visual Basic for Applications) to create and modify macros. The Macro Recorder is a useful starting point, while VBA allows you to customise automation tasks further.
What to learn: macro recording, running saved macros, basic VBA instructions and saving macro-enabled workbooks. For more detailed training in macro creation and VBA programming, explore the Excel VBA Course in Singapore.

15. Protecting Worksheets and Managing Shared Files

When an Excel workbook is shared with several employees, important formulas or headings can be changed accidentally. Excel worksheet protection helps control which cells users can edit.
Useful features include:

Protect Sheet: Restrict editing within a worksheet.
Protect Workbook: Control changes to workbook structure.
Unlock Cells: Allow users to edit selected input fields.
Comments and Sharing: Support communication and collaboration when reviewing information.

Workplace example: A finance team can share a budget sheet where employees enter expenses in designated cells while protecting the cells containing calculation formulas.
Remember that worksheet protection helps prevent accidental changes; it is not a replacement for file encryption or proper security controls.
What to learn: Worksheet protection, workbook structure protection, locked and unlocked cells, and sharing options.
If you want to develop these skills further, explore advanced Excel functions and tools, including complex formulas and workbook automation.

Putting Your Excel Skills into Practice

Knowing individual Excel functions is useful, but applying them together is what helps you handle complete workplace tasks.
Try creating a sales report that uses an Excel Table to organise records, SUMIFS to calculate regional sales, XLOOKUP to retrieve product details and a PivotTable to summarise monthly results. You can then use Formula Auditing to check calculations and protect the worksheet before sharing it.
This type of exercise brings together basic, intermediate and advanced Excel skills in one workbook.

You can also learn how advanced Excel skills support career growth across different workplace roles.

4. Which Excel Skills Should You Learn for Your Job Role?

Not every workplace role requires the same level of Excel knowledge. An administrative employee may need formulas and data organisation, while someone working in finance may regularly use PivotTables, lookup functions and advanced calculations.
Here is a quick guide to the Excel skills commonly used across different roles:

Job Role	Excel Skills to Focus On
Administration and HR	Data entry, formatting, IF, COUNTIF, sorting and filtering
Finance and Accounting	SUMIFS, XLOOKUP, PivotTables, formula auditing and financial calculations
Sales and Marketing	Excel Tables, conditional formatting, charts, lookup functions and sales reports
Operations	Data validation, sorting, filtering, PivotTables and report consolidation
Data and Reporting	Advanced formulas, PivotTables, Power Query and data analysis

If your work involves preparing reports, comparing figures or managing large spreadsheets, learning intermediate and advanced features can help you handle these tasks more efficiently. You can explore the Intermediate and Advanced Excel course to understand the topics covered at these levels.
The best starting point is to identify the Excel tasks you perform regularly and learn the features that help you complete them accurately.

5. How to Progress from Basic to Advanced Excel

Learning all 15 Excel skills at once can be difficult, especially if you are new to spreadsheets. A step-by-step approach helps you understand each feature and apply it to everyday tasks.
You can follow this learning sequence:

Start with the basics: Learn workbook management, data entry, cell formatting and basic formulas such as SUM and AVERAGE.
Practise intermediate functions: Work with IF, COUNTIF, SUMIFS and lookup functions using sample sales, attendance or expense data.
Learn data organisation: Create Excel Tables, apply filters, use conditional formatting and manage drop-down lists.
Move to advanced reporting: Practise PivotTables, advanced formulas and calculations across multiple worksheets.
Explore automation and protection: Learn to record macros, understand basic VBA and protect important worksheet data.

Practise with Workplace Examples

Reading about Excel functions is not enough. You need to use them in tasks similar to the ones you may handle at work.
For example, create an employee attendance sheet and use COUNTIF to calculate present days. Next, prepare a monthly sales report using SUMIFS and summarise the results with a PivotTable. These exercises help you understand how different Excel features work together.
You can also practise with existing spreadsheets, check your formulas and try different functions to complete the same task. This will help you become more comfortable with Excel as the complexity of your work increases.

Conclusion
Excel skills develop gradually, from entering and organising data to using formulas, analysing information and automating repetitive tasks. Start with the features you need for your current work, practise them with sample datasets and move towards more advanced functions as your requirements grow.
Whether you are a beginner or already use Excel at work, improving your Microsoft Excel skills can help you manage spreadsheets, prepare reports and handle workplace data more accurately.

From Basic to Advanced Excel: 15 Essential Skills to Learn for the Workplace