Blog

# How to create a Hierarchy SmartArt Graphic in Excel

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, will help you create your own hierarchy. Our example scenario is of a Report Store that contains reports created for […]

# Using the VLOOKUP formula with the approximate match in Excel

Have you ever used the VLOOKUP formula with the approximate match in Excel? By using this formula, you’re able to extract the correct trade discount percentage based on the quantity of a specific item purchased. In this tip, we demonstrate how to set this up. If TRUE or 1 is specified in the Range_lookup argument text […]

# How to create more accurate forecasts with Excel

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 are welcome to download the workbook to practice! 1. To create a Forecast […]

# Inserting 3D models into an Excel spreadsheet

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 much fun to play with. In this tip, I’m […]

# Learn how to navigate through your data quickly with keyboard shortcuts in Excel

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 or cells. An obvious way to check this is by […]

# Learn how to extract more than one column of data with a single VLOOKUP formula in Excel

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 work. Last week, a financial manager asked me how she could easily look up more than one field of data […]

# How to use a slicer instead of a drop-down list in Excel

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 of selecting data from a list. In this tip, we explain how to use […]

# How to calculate the average useful life of assets

To complete an analytical review of the depreciation of your company’s assets, you can calculate the average useful life of these assets. The average useful life, which is a measure of conditional central tendency, can be calculated using the AVERAGEIF function. Central tendency is the middle value or a typical value within a data set. […]

# How to effectively use custom number formatting to display your + or – values

In order to effectively analyze data, custom number formatting can be applied to your Microsoft Excel spreadsheet. Custom number formatting is used to easily identify values based on a set criteria. In a large Excel spreadsheet, you can easily highlight all negative or positive values by using custom number formatting. In this example, we are going to […]

# 3 Easy steps to hide zero values in Excel

To have neat looking reports in Microsoft Excel, you may decide that you want to hide zero values. In this tip, we explain how to hide zero values for selected cells by using the custom number format. The hidden zero values appear in the formula bar when a cell is selected. Values that change to […]