1. Freeze Panes

When to use it? This feature allows you to lock specific rows or columns. As you scroll, these remain visible, making data comparison easier.
Learn the skill: First select or activate the cell, row or column where you want to freeze panes, then: Navigate to View > Freeze Panes.

2. Quick Analysis Tool

When to use it? A tool that offers quick access to visualisations, tables, and other features based on your selected data.

Learn the skill: Select your data and click the small icon at the bottom right.

3. Flash Fill

When to use it? Excel detects patterns and auto-completes the rest of the data for you.

Learn the skill: Start typing a pattern, and Excel will suggest completions. Press Enter to accept.

4. Use of Name Ranges

When to use it? Instead of remembering specific cell references, you can name a particular range.

Learn the skill: Select the range, go to Formulas > Define Name, and assign a name. You can also edit and manage names in Name Manager.

5. AutoSum

When to use it? A quick way to sum up a range of numbers.

Learn the skill: Select the cell where you want the sum to appear, then click AutoSum in the Home or Formulas tab.

6. Drop-down Lists

When to use it? Create a drop-down list in a cell to ensure data consistency.
Learn the skill: Select the cell, go to Data > Data Validation, and choose ‘List’ in the criteria. Tip: Be sure to prepare your list first!

7. Format Painter

When to use it? Copy the format from one cell and apply it to another cell or range of cells.

Learn the skill: Click the cell with the desired format, then click the Format Painter icon in the Home tab. Next, select the cell or range of cells where you want to apply the format with your mouse.

8. Remove Duplicates

When to use it? Quickly eliminate duplicate rows in your data.

Learn the skill: Select your data range, then select Data > Remove Duplicates. Tip: it's a good idea to create a (temporary) copy of you data first.

9. Text to Columns

When to use it? Split text into multiple columnsin a single column.

Learn the skill: Select the column with the text, then choose Data > Text to Columns and follow the wizard.

10. CONCATENATE Function

When to use it? Combine text from multiple cells into one cell.

Learn the skill: In a new cell, type =CONCATENATE(A1,” “, B1) to combine the text from cells A1 and B1 with a space in between. Tip: The concatenate function is also very useful within calculation (sum, count etc) and lookup or indexing formulae where you need a custom or specific lookup value. We call these data keys.

11. Hide and Unhide Rows/Columns

When to use it? Temporarily remove rows or columns from view without deleting them.

Learn the skill: Right-click the row or column header and select ‘Hide’. To unhide, select the surrounding rows or columns,
right-click, and choose ‘Unhide’.

12. Data Bars

When to use it? A form of conditional formatting that adds a coloured bar to a cell, representing the cell’s value compared to others.

Learn the skill: Select the range, go to Home > Conditional Formatting > Data Bars, and choose a colour.

13. Use of $ for Absolute References

When to use it? Lock a cell reference so it doesn’t change when copying formulas.

Learn the skill: In a formula, place a $ before the column, row, or both. For example, $A$1 locks the reference to cell A1.

14. Quick Access Toolbar

When to use it? A customisable toolbar at the top of Excel provides shortcuts to frequently used commands.

Learn the skill: Click the small arrow at the top, then choose ‘More Commands’ to add or remove tools.

15. Keyboard Shortcuts
When to use it? Use combinations of keys to perform tasks quickly without using your mouse.

Examples:

Ctrl + Z: Undo

Ctrl + Y: Redo

Ctrl + C: Copy

Ctrl + V: Paste

Ctrl + X: Cut

Ctrl + Arrow Keys: Navigate between data points

Ctrl + Shift + Arrow Keys: Navigate between data points and slect the data at the same time.

19. Advanced Filter

When to use it? Allows you to filter data based on complex criteria.
Learn the skill: Select your data range, goto Data > Advanced and set up your criteria range.

20. VLOOKUP Function

When to use it? Search for a value in the first column of a table range and return a value in the same row from a specified column.

Learn the skill: =VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup]).

21. HLOOKUP Function

When to use it? Similar to VLOOKUP, but searches for values in the specified row.

Learn the skill: =HLOOKUP(lookup_value,table_array, row_index_num, [range_lookup]).

22. PivotTables

When to use it? Summarize large datasets by organising and comparing data based on your selections.

Learn the skill: Select your data range, then goto Insert > PivotTable.

23. Error Checking

When to use it? Identifies common errors informulas.

Learn the skill: Go to Formulas > Error Checking.

24. Trace Precedents & Dependents

When to use it? Visualise which cells affect thecurrent cell’s value (precedents) or which cells depend on the current cell’s value (dependents).

Learn the skill: Select a cell, then go to Formulas > Trace Precedents/Trace Dependents.

25. Data Tables

When to use it? A tool to see the impact of one or two variables on a formula.

Learn the skill: Set up a data table and goto Data > What-If Analysis > Data Table.

26. Scenario Manager

When to use it? Compare different scenarios in your data without altering the actual data.

Learn the skill: Go to Data > What-If Analysis > Scenario Manager.

27. Custom Number Formatting
When to use it? Define your own number formats.
Learn the skill: Right-click a cell, choose ‘Format Cells’, and navigate to the ‘Number’ tab.

28. Create Custom Lists

When to use it? Define your own lists for auto-fill (e.g., a list of department, employee, customer names).

Learn the skill: Go to File > Options > Advanced > Edit Custom Lists.

29. Use of Camera Tool

When to use it? Take a live picture of a range of cells. The picture updates if the data changes.

Learn the skill: Add the Camera tool to theQuick Access Toolbar, select a range, click the Camera tool, then click where you want the picture.

30. Sparklines

When to use it? Mini charts placed in single cells to quickly visualise data trends.

Learn the skill: Select the cell where you wantthe sparkline, then go to Insert > Sparklines.

31. Wildcards in Formulas
When to use it? Use symbols like * and ? to represent characters in formulas.
Learn the skill: In functions like VLOOKUP or MATCH,use * to represent multiple characters and ? for a single character.

32. External Data Connections

When to use it? Link your Excel workbook to external data sources, such as databases or other Excel files.

Learn the skill: Go to Data > Get External Data and choose your source.

33. Watch Window

When to use it? Monitor the values of certain cells when they are not in view.

Learn the skill: Go to Formulas > Watch Window and add the cells you want to monitor.

34. Evaluate Formula

When to use it? Break down a formula into its calculated components.

Learn the skill: Select the cell with the formula, then go to Formulas > Evaluate Formula.

35. Formula Auditing

When to use it? Visualise and trace the relationships between cells to debug formulas.

Learn the skill: Go to Formulas > Formula Auditing and use tools like “Trace Precedents” and “Trace Dependents”.

36. Protect Workbook and Worksheets
When to use it? Secure your workbook or specific sheets with a password.

Learn the skill: Go to Review > Protect Workbook or Protect Sheet. Set a password and permissions as needed.

37. Array Formulas

When to use it? Formulas that perform multiple calculations on one or more items in an array.

Learn the skill: Enter your formula and press Ctrl + Shift + Enter instead of just Enter.

38. Solver Add-in

When to use it? A tool that determines the optimal value of a target cell by changing values in cells used to calculate the target cell.

Learn the skill: Go to Data > Solver. If it’s not there, you might need to install it via File > Options> Add-Ins.

39. Custom Views

When to use it? Save specific views of your workbook (like display settings or hidden rows/columns) and switch between them.

Learn the skill: Go to View > Custom Views to add or apply a view.

40. INDIRECT Function

When to use it? Returns a cell reference from atext string.

Learn the skill: =INDIRECT(“A”&”1”) would return the value in cell A1. Tip: This can also be used to get a value in a cell or cells on a different sheet.

41. Data Model and Relationships

When to use it? Use multiple tables of data in a PivotTable or PivotChart without VLOOKUP.

Learn the skill: Go to Power Pivot >Manage to create relationships between tables.

42. Slicers and Timelines

When to use it? Interactive tools that filter PivotTables, PivotCharts, or Excel tables.

Learn the skill: With your data selected, goto Insert > Slicer or Insert > Timeline.

43. Power Pivot

When to use it? An Excel add-in that lets you perform powerful data analysis.

Learn the skill: Go to Insert > PowerPivot Window.

44. DAX Formulas

When to use it? Advanced formulas used in PowerPivot.

Learn the skill: In the Power Pivot window, you can create calculated columns or measures using DAX.

45. Power View

When to use it? A data visualisation tool for creating interactive charts, graphs, maps, and other visuals.

Learn the skill: Go to Insert > Power View. Note: This might require an add-in.

46. Dynamic Arrays

When to use it? Formulas that return multiple values that spill into adjacent cells.

Learn the skill: Use functions like FILTER, SORT,or UNIQUE, and Excel will automatically output the results toa range of cells.

47. XLOOKUP Function

When to use it? A modern replacement for VLOOKUP/HLOOKUP that doesn’t require sorting and can return vertical or horizontal data.

Learn the skill: =XLOOKUP(lookup_value,lookup_array, return_array).

48. LET Function

When to use it? Allows you to assign names tocalculation results within a formula.

Learn the skill: =LET(name1, name_value1,[name2], [name_value2], … formula).

49. LAMBDA Function

When to use it? Create custom, reusable functions without VBA.

Learn the skill: Define a LAMBDA function and then call it in your workbook.

50. Dynamic Data Types

When to use it? Cells that have a rich set of related information that can be easily extracted.

Learn the skill: Enter data like a stock ticker, and Excel will recognise it. You can then extract related information using the dot operator.

1. How do I protect sensitive information in an Excel workbook?

To protect sensitive data, you can password-protect your workbook by going to File > Info > Protect Workbook. You can also protect specific sheets or cells from being edited by navigating to the Review tab and selecting “Protect Sheet” or “Protect Workbook”. For even more advanced protection,
it is also possible to use VBA macros to completely hide (or show) sheets in your workbook.

2. How do I use Excel to forecast data trends?

Excel’s Forecast Sheet feature enables you to predict futuretrends based on historical data. Select your data, go to the Data tab, and
click on “Forecast Sheet”. Excel will create a forecast chart, allowing you to visualize future trends. Of course this is very basic forecasting, for a more accurate and controlled approach that applies specifically to your business, we’d advise using a Financial or Data Analyst to build a forecasting process for you tat uses your specific criteria and meets your exact requirements.

3. How can conditional formatting be used to highlightkey data points?

Conditional formatting allows you to apply formatting to cells that meet specific criteria. Select your cells, go to the Home tab, and choose “Conditional Formatting”. You can highlight cells based on their values, text, dates, or create custom rules to visualise data trends or outliers.

4. What is the quickest way to remove duplicates from a dataset in Excel?

To quickly remove duplicates, select your data range,navigate to the “Data” tab, and click on “Remove Duplicates”. You can then select which columns to check for duplicates, and Excel will delete any found, leaving only unique rows. Tip: We would strongly advise that you always perform this function on a copy of your data first as it can result in data destruction. Once you are happy with the results you can delete the copy or backup of your data or Excel sheet.

Efficient use of Microsoft Excel is still essential in 2025 for those utilising data regularly that they either need to manage efficiently or visualize to drive business decisions.

While many employees are Excel users and can use Excel, for more advanced solutions and / or to simply reduce the overhead on the internal workforce more and more companies are outsourcing specific Excel based tasks or Excel based projects, especially where VBA automation, data extraction, data transformation or extensive data clean-up is required.