Excel Tips & Tricks

It is sometimes not always easy to trace which data point relates to which series in a chart, especially when it comes to a line chart.  To overcome this problem, we can dynamically highlight an individual data point. Let’s get started. Do...

Named ranges are an essential feature in Microsoft® Excel®.  They are easy to use in formulas and can be used anywhere in a workbook. The trick comes in when you have to remember all the defined names you have created.  An easy way ...

When viewing numerical data in Excel, you may at some point want to represent this data visually. This gives you the ability to easily analyze large amounts of data. Charts and graphs also make your report look more professional and can even help whe...

When using a data validation list, only one column of data is displayed, but sometimes there is a requirement for additional columns to be displayed simultaneously.  The multi column data validation list gives you the ability to look up data usi...

Have you ever experienced your Excel charts disappearing when you hide the columns showing the data? By default, Microsoft  Excel  shows only visible data in a chart. In this tip, we will show you how to easily overcome this problem, by f...

This Microsoft  Excel  tip will come in handy when you want to create a chart that will populate your data and highlight only specific parts of that data. Download the workbook to practise this exercise. In the example below, w...

A step chart shows changes that occur over irregular intervals. For example, it can show stock movement changes, interest rates, etc. A step chart  is the perfect alternative to a line chart as it displays the trend as well as the time take...

Data visualisation is fast becoming the norm for effective business intelligence. Having visuals that tell the right story at a glance go a long way in achieving this. Microsoft  Excel  provides you with a multitude of ways to enhance the...

These Microsoft   Excel   tips and tricks are the top performing tips of 2018, and have raked up a combined viewership of over 200 000. Without further ado, here they are, the Top 5 Excel tips and tricks of 2018.  How to con...

In a previous tip, we explained how you can easily remove duplicate rows from your data using the Remove Duplicates function.   You can find it here.   At times though, you may just want to highlight duplicate rows without actually deleting...

Have you ever had the pleasure (or displeasure) of working with an extra large Excel workbook with many, many different sheets? This happens frequently, especially if you are working with a large amount of data that needs to be analyzed periodically....

In our example workbook, we're working with Date, Value In and Value Out fields. We need to create a Pivot Table that will also display the variance per month, as well as a running total of the variance for the year. In this tip, we show you how to d...

This tip is useful when you want to replace or find text using Wildcards, e.g. if you have a column where you would like to replace everything after the space character with text. What are Wildcards? Wildcards are characters that are used to repres...

Sorting data in alphabetical order or numerical order is helpful when you have large amounts of data in the PivotTable you created. Sorting lets you organize the data so it is easier to find the items you want to analyze. 1. Within the PivotTable, c...

This tip may seem basic, but with Microsoft continually adding value to its products—we just had to mention it. It's lead by a repository of every icon imaginable (and maybe even a few that are unimaginable) that you may find useful, especially if yo...

Have you ever felt the need to effortlessly switch the date ranges within your Pivot Table in Excel? Have you ever wanted to view the data for just one specific time-frame without having to apply a logical filter all the time? When is the last time y...

In a previous tip, we showed you how you can navigate around a data list quickly using keyboard shortcuts.   To view the tip, take a look here.   By combining the same shortcuts with the Shift key, you can easily make selections in your dat...

Let's say you have a workbook containing sales over a two year period. You would now like to know what the average is for your top 5 sales values, as well as the value for your largest sale. In our example, which you are welcome to download here, we...

Sometimes, large Microsoft Excel workbooks can be slow and inefficient—which could cause frustration.   To reduce the size of a workbook, consider replacing several formulas with a single array formula. Array formulas can perform mult...

Microsoft Excel offers you the ability to create and design a Hierarchy SmartArt Graphic.   This is useful in showing a graphical representation of an organogram, organisation chart or any hierarchy structure. This example, explained below, wil...

Do you need to future-proof your business? The Excel Forecast sheet may be the right tool for you! The Forecast sheet allows you to take your historical business data (sales, cash flow, etc.) and create more accurate forecasts for the future. You ar...

This is a tip that you're going to really enjoy if you love imagery and visualisations in your spreadsheet! It's not the most useful tip in the world, but for most people, especially the creative types—it will put a smile on your face because it's so...

When working with large data lists in Excel, you may need to know the extent of the data, i.e. the number of rows and columns.   If consistency of the data is important too, then you may need to identify whether there's any empty rows, columns o...

It's been well over 10 years since I started training Microsoft  Excel  to corporate clients. During training sessions, delegates would usually ask questions on how they can use Excel to help them become more effective in their wo...

Selecting data from a drop-down list is a convenient and accurate way of entering data into a range. It helps in ensuring that the correct data is entered quickly in Microsoft  Excel. However, a slicer provides a quicker, better and easier way o...