Home » Blog » Excel Tips and Tricks

Using the VLOOKUP formula with the approximate match in Excel May 10, 2018

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… Read more

How to create more accurate forecasts with Excel May 9, 2018

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!… Read more

Inserting 3D models into an Excel spreadsheet May 2, 2018

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… Read more

Learn how to navigate through your data quickly with keyboard shortcuts in Excel April 25, 2018

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… Read more

Learn how to extract more than one column of data with a single VLOOKUP formula in Excel April 18, 2018

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… Read more

How to use a slicer instead of a drop-down list in Excel April 11, 2018

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,… Read more

How to calculate the average useful life of assets April 4, 2018

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… Read more

How to effectively use custom number formatting to display your + or – values March 29, 2018

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… Read more

3 Easy steps to hide zero values in Excel March 22, 2018

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… Read more

How to compare and merge a shared workbook in Excel March 14, 2018

Sometimes when working in Excel and collaborating with your colleagues on a single document, you may wonder to yourself; how can I compare and merge a shared workbook? The answer is simple! You can do this by using the Compare and Merge Workbooks command (this command will have to be… Read more

3 Easy steps to help you complete a cash flow template March 8, 2018

In order to correctly show the movement and management of cash within a business, accountants use a cash flow statement. This statement will display a business’s money coming in from ‘cash receipts’ and where it has paid money out, ‘cash paid’. The cash flow statement is a standard financial statement… Read more

Learn how to forecast with scenarios using what-if analysis tools in Excel February 28, 2018

The Scenario Manager is a feature in what-if analysis and you can use this to enhance your forecasting in Microsoft® Excel®. A scenario is a set of values that Excel saves and can automatically substitute in specific cells on a worksheet. You can create and save different groups of values… Read more

Quickly split data into two or more columns in Excel February 21, 2018

If you ever need to split data from one column in your Microsoft Excel worksheet into two or more columns, you can use the LEFT, MID and RIGHT Text functions. The LEFT function returns the first character or characters in a text string, based on the number of characters you specify. The… Read more

Using conditional formatting with custom Excel formulas February 14, 2018

Conditional formatting is an effective Microsoft Excel feature that allows you to highlight important information, for example; the ability to find duplicate values within your spreadsheet. You can create your own rule by applying conditional formatting to individual cells or a range of cells. When you have selected the data… Read more

How to calculate Sales Tax/ VAT using PowerPivot Measures in Excel February 7, 2018

In a previous tip, we showed you how to set up a PivotTable using PowerPivot. You’re now going to learn how you can easily calculate Sales Tax/ VAT using the Measures feature in the PowerPivot tab. If you’re the business’s accountant, you may want to analyze the Sales Tax/ VAT that will… Read more

How to determine whether you are over or under budget using custom formats January 25, 2018

Monitoring budgets can be quite stressful and may often involve the use of formulas. Instead of complicated formulas, you can use custom formats within Microsoft Excel to easily determine if you are over or under budget. All it takes are a few steps, which we explain below, and you can… Read more

How to create a hyperlink to a specific location in a workbook November 29, 2017

For quick access to related information in another file, workbook or on a web page, you can insert a hyperlink in a Microsoft® Excel® worksheet cell. You can also insert hyperlinks in specific chart elements or images. If you struggle to navigate to various worksheets in a large workbook, then… Read more

How to view or compare two or more worksheets at the same time in Excel November 15, 2017

You can quickly view or compare two or more worksheets in the same workbook, or in different workbooks, by viewing them side by side. You can also arrange multiple worksheets to view them all at the same time. In this tip, I show you how. Applies To: Microsoft® Excel® for… Read more

Collaborating on a Shared Workbook November 8, 2017

Multiple users can effectively work on a spreadsheet by sharing it, and then collaborating on it. Edits can be made simultaneously by team members, thus having a continuously updated version of the spreadsheet available all the time. In this way, version control is enforced, and duplicate work is eliminated. Applies… Read more

Discover how to compare two versions of a workbook in Excel October 25, 2017

Sometimes when working in Excel, you may want to compare two versions of a workbook to see what was changed. It could be an issue such as totals that are manually entered instead of formula calculated that you want to check. You can use Microsoft Spreadsheet Compare to run a… Read more

How to work with numbers containing more than 15 digits in Excel September 29, 2017

Numbers containing more than 15 digits in Excel are not often used, however some users might use them when recording credit card numbers, account numbers, stock codes, etc. Applies To: Microsoft® Excel® for Windows 2013 and 2016. Excel can’t handle more than 15 digits per cell, and so when these… Read more

How to work with numbers containing more than 15 digits in Excel September 21, 2017

Numbers containing more than 15 digits in Excel are not often used, however some users might use them when recording credit card numbers, account numbers, stock codes, etc. Applies To: Microsoft® Excel® for Windows 2013 and 2016. Excel can’t handle more than 15 digits per cell, and so when these… Read more

How to fix wrapped data labels in a pie chart September 1, 2017

When creating a pie chart and adding data labels to the slices, the wording can sometimes split into two lines—giving you wrapped data labels that aren’t visually appealing. In the below example, Administration has been split into two words, each on a separate line. The labels are wrapped and the… Read more

How to view statistics on the status bar in Excel August 25, 2017

This tip helps you discover how to quickly display statistics on the status bar by simply highlighting data. You are welcome to download the workbook to practice. Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016. Excel allows for up to 6 statistic options to be viewed on the… Read more

How to print and display formulas in an Excel workbook August 18, 2017

Instead of displaying values, you can actually display formulas as a way of reviewing the spreadsheet. In this tip, we explain how that can be done. You are welcome to download the workbook to practice. Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016. To show all formulas in a… Read more

How to generate a barcode in Excel August 11, 2017

This tip shows you how to generate a barcode in Excel that will enable you to track stock, sales, purchases or fixed assets. Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016. Excel has no built-in functionality to generate a barcode.  However, this is easily achievable by simply downloading and… Read more

How to Remove PivotTable Fields from Pivot Charts August 4, 2017

When creating Pivot Charts from PivotTables, the PivotTable fields will be displayed on the chart. In this tip, you’ll learn how to quickly hide them so that your report looks neater. Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016. You are welcome to download the workbook to practice this… Read more

How to remove spaces and characters in a cell July 28, 2017

If you have ever had problems sorting, filtering, or manipulating data in a Microsoft® Excel® workbook, and realized that it was caused by stubborn leading spaces in the cells, this tip will show you how to remove spaces quickly the next time. If you’re wondering what causes the spaces, there… Read more

How to Subtotal Dates by Month and Year in Excel July 21, 2017

If your data has a column of dates, you may want to subtotal dates by month and year. However, when using the Subtotal command, the desired result is not being achieved as it creates a subtotal for each day. You are welcome to download the workbook to practice. Applies To:… Read more

Avoiding the Ref# error: How to convert formulas to values in Excel July 14, 2017

Sometimes when working with spreadsheets in Excel, you may want to distribute a workbook with static values. If your worksheet has formulas that are linked to other worksheets, you may end up with the dreaded Ref# error when distributing. That is why it is important to convert the formulas to… Read more

How to restore missing labels in Excel July 7, 2017

Manipulating data with missing labels can pose a challenge. Copying and pasting data can be time consuming and tiresome. That is why, in this tip, we show you how to quickly restore data labels using a simple formula. If you find value in this tip, why not sign up for our… Read more

View specific aspects of several different worksheets on one worksheet June 30, 2017

One worksheet can be used to view specific aspects of several different worksheets. This can be done using the Linked Picture option. There are a few options when consolidating information from multiple worksheets, and in this tip, we’ll deal with the most popular. The option for this tip enables you… Read more

How to avoid the VALUE error when using the SUMIF function between workbooks June 23, 2017

When using the SUMIF function between workbooks, you may get a VALUE error if the source workbook is not open. This behavior occurs when the formula that contains the SUMIF, COUNTIF, or COUNTBLANK function refers to cells in a closed workbook. To work around this, use a combination of the… Read more

How to delete data but keep your formulas on a worksheet in Excel June 16, 2017

Have you ever found that at the beginning of the financial year you need to make a copy of the previous year’s workbook? If so, you’d need to delete all the old year’s data, but you would really like to keep your formulas in the worksheet. By using the Go… Read more

How to quickly convert negative values to positive values June 9, 2017

Sometimes converting negative values to positive values can be time-consuming. This tip allows you to quickly convert a range of negative values into positive values, without having to edit each cell. Applies To: Microsoft® Excel® for Windows 2010,2013,2016. 1.In a blank cell, enter the value -1. 2. Select the cell… Read more

How to round a number off to the nearest multiple in Excel June 2, 2017

If you need to round a number off to a specified multiple, for example; rounding a selling price to the nearest 5cents or rounding a time to the nearest 15 minutes—then MROUND is the function to use. Below we explain how that can be done. Applies To: Microsoft® Excel® for Windows… Read more

How to Print a List of Named Ranges in Excel May 19, 2017

It is very easy to create a large list of named ranges but to manage those ranges can be confusing, especially if you receive the workbook from somebody else. Having a list of the named ranges in your workbook could be helpful. To get a list, follow these steps: Applies… Read more

How to calculate Sales Tax/ Vat using PowerPivot Measures in Excel May 12, 2017

In a previous tip, we showed you how to set up a PivotTable using PowerPivot. This week, you’re going to learn how you can easily calculate Sales Tax/ Vat using the Measures feature in the PowerPivot tab. If you’re the business’s accountant, you may want to analyze the Sales Tax/ Vat that… Read more

How to use keyboard shortcuts in Microsoft Excel to increase productivity May 5, 2017

Using keyboard shortcuts in Microsoft Excel helps you increase your efficiency and productivity, and helps you to use Microsoft Excel like a pro. In this Tip, we take a look at how we can use Excel shortcuts to save us time and increase our business efficiency. We will learn how… Read more

How to use the IFS Function in Microsoft® Excel® April 28, 2017

The IFS function gives you an efficient alternative to the tedious task of creating a nested IF function. The IFS function checks conditions sequentially, and as soon as a condition is found to be true, the function stops and returns the specified value. You can take advantage of this logic… Read more

How to Create a 3D Map in Excel 2016 April 21, 2017

With a 3D Map, you can plot geographical data and view it on a 3D globe or custom map. If your data is time-stamped, you can even use a 3D Map to get a geographical tour of how your data changes over time. To create a 3D Map, your data… Read more

3 PivotTable Improvements in Microsoft Excel 2016 April 7, 2017

The PivotTable is one of the easiest tools used to transform huge amounts of data into meaningful reports. Excel didn’t disappoint with the improvements that have been made to PivotTables in their 2016 version. These include the following; Field List search, Automatic Time Grouping, and Multi-select Slicers. In this tip,… Read more

How to prevent objects from distorting in Excel March 17, 2017

When an object has been placed in the same row as your data and you delete some of your rows, you may find that your object will either be compressed or deleted completely.  This can easily be avoided by changing a setting in your object properties, thus preventing your object… Read more

How to get a distinct count in a Pivot Table March 10, 2017

Do you need to know how many customers you have invoiced this month?  When creating a Pivot Table and adding your customers to both the row labels and again in the value area, each transaction is totaled for each customer.  This does not give a true reflection of how many… Read more

3 Easy steps to create a forecast report in Microsoft® Excel® 2016 February 24, 2017

Have you ever needed to create a forecast report in Microsoft® Excel®? How did you calculate the projections? Whichever methods you used, Microsoft has made this much simpler with a new feature called Forecast Sheet. All you need to do is provide the input, and with a single click of the… Read more

How to automatically open a frequently used workbook February 17, 2017

If you start each day needing to use a frequently used workbook, e.g. daily sales report, to-do list, calendar, etc, then this tip is for you.  You can automatically open these workbooks, by simply adding the workbooks or a shortcut in the XLStart folder.  This is a specific folder that Microsoft®… Read more

How to extract the formula text in Excel February 10, 2017

Auditing and troubleshooting formulas in Microsoft® Excel® can be time consuming and difficult. That is why the FORMULATEXT function is helpful because it simplifies formulae error checking and correction. The formula text can easily be extracted without having to copy and paste. The FORMULATEXT function syntax has one required input—a… Read more

How to hide zero values using Macros in Microsoft® Excel® February 3, 2017

Do your financial and operational reports include rows with zero values? Do you feel that your reports would be neater and easier to read if you could hide these rows?  In this tip, we will show you how to hide zero values using Macros. Applies To: Microsoft ® Excel® 2010,… Read more

How to print different headers and footers for odd and even pages January 27, 2017

If you’ve created a Microsoft® Excel® report that needs to be printed double-sided (e.g. booklet form), you may want to have your headers or footers printed on different sides of the paper—left hand side for odd pages, and right hand side for even pages. To achieve this effect, follow this… Read more

3 Easy steps to hide zero values in Excel December 16, 2016

In order to have neat looking reports in Microsoft® Excel®, you may choose 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.… Read more

How to enhance your chart data series using a pictograph December 9, 2016

Charts are used for displaying data graphically, and they can be enhanced by using a pictograph. Pictographs use any picture you choose to represent as your data series in the charts. Icons can also be used as pictograph to represent data values in order to decrease the size of the… Read more

Easily move a row or column in Excel December 2, 2016

When wanting to move a row or column in Excel from one section of a worksheet to another (e.g. from row 13 to row 10), it’s normal practice to use the Cut and Insert Cut Cells command. However, an easier and quicker way to do this is the following: Select… Read more

How to prevent slicers from ruining filtered charts in Excel November 25, 2016

          Charts are used for creating a graphical presentation of the data.  A common practice when creating charts is to create them off of a filtered Pivot Table, usually a Top 5 or a Top 10 Pivot Table.  Slicers can then be added to interactively filter… Read more

How to quickly rearrange your worksheets in alphabetical order November 18, 2016

Are you looking for a shortcut to quickly rearrange your worksheets in alphabetical order?  Instead of dragging and manually arranging your worksheets, you can use VB for Microsoft® Excel® to quickly rearrange them into alphabetical order. Excel currently has no built-in function to rearrange your worksheets. Therefore, if you have… Read more

How to enhance your Excel dashboards with Power View reports November 11, 2016

Dashboards in Microsoft® Excel® are used for visually presenting important business information. Dashboards can display current status of metrics and key performance indicators. In this tip, we look at how to enhance dashboards with a Power View report by analyzing sales data. Power View reports in Microsoft® Excel® pull your… Read more

An easy way to count cells that are not empty in Excel November 4, 2016

Let’s say you want to count cells that contain the number of employees in a data range, and would like to ignore any blank cells. The way to go about doing this is to use the COUNTA function. The COUNTA function is a way to count cells containing any type… Read more

How to easily double-check your Excel formula October 28, 2016

When creating an Excel formula, sometimes the result you get is not quite the one you expected, and if a complex formula is being used, it can be time consuming to find where the error is occurring. If you’re ever writing a long formula and it doesn’t give you the… Read more

How to use a slicer instead of a drop-down list in Excel October 21, 2016

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… Read more

How to create a Tree Map chart in Excel 2016 October 14, 2016

The Tree Map chart is a new chart type available in Microsoft® Excel® 2016. It’s used to proportionally display hierarchical levels of data in the form of different sized rectangle blocks. A sales manager can use the Tree Map chart to monitor the performance of the sales consultants. This chart… Read more

How to convert dates into the correct format in Excel October 7, 2016

When extracting data from a database, dates don’t always show up in the format you want them to. That’s why it’s helpful to know how to use the Text to Columns option to convert dates into a format that can be used to easily analyze or work with data. As… Read more

How to create quarters in Excel September 30, 2016

Currently, there’s no function that will convert dates into quarters in Microsoft® Excel®.  However, using the CHOOSE function, we can easily convert dates into quarters in Excel. The CHOOSE function returns a value from a list of items based on a position number. For example, if we had a function… Read more

Quickly split data into two or more columns in Excel September 23, 2016

If you ever need to split data from one column in your Microsoft® Excel® worksheet into two or more columns, you can use the LEFT, MID and RIGHT Text functions. The LEFT function returns the first character or characters in a text string, based on the number of characters you… Read more

How to change the default font size for Excel comments September 15, 2016

Adding a comment to a cell in Microsoft® Excel® is a quicker way of providing feedback. Instead of typing out an email to provide feedback, you can simply enter important information straight into the spreadsheet. However, you may want to change the default font size for Excel comments because it’s… Read more

How to quickly rearrange columns in a data table in Excel September 9, 2016

As with most commands in Microsoft® Excel®, there’s an easy way and a long way to rearrange columns in a data table. The long way would be to highlight a column, cut it, and then paste it where you want to move it to. In this tip, I’m going to… Read more

Learn how to categorize debtors balances in Excel using icon sets August 18, 2016

As an accountant, you can easily categorize debtors balances by using the conditional formatting – icon sets option. Icon sets creates a visual effect in your data and helps you to see how the value of a cell compares to other cells. In the example below we categorize the debtors… Read more

5 Easy steps to enhance your dashboards in Excel using the camera tool August 11, 2016

The camera tool enables you to take a picture of a range of cells and display it in a preferred way, thus allowing you to enhance your dashboards. The picture will automatically update when the data in the original cells change. In this example, the column Sparkline chart will be rotated to… Read more

5 Easy steps using the combination chart in Excel to help you monitor budgets July 20, 2016

There are many different charts in Microsoft® Excel®, but only one that allows you to highlight different types of information by combining two or more chart types into one. The combination chart can be used to see how far away a value is from the target or if it is… Read more

Learn how easy it is to reduce the size of an Excel workbook using a single array formula July 14, 2016

We often find ourselves working with Microsoft® Excel® workbooks that contain large amounts of data, causing them to process instructions slowly. To reduce the size of an Excel workbook, consider replacing several formulas with a single array formula. Array formulas can perform multiple calculations and ensure consistency which can lead… Read more

3 Easy ways to distribute your Sage Intelligence reports with or without embedded formulas July 13, 2016

In Sage Intelligence Reporting you have the ability to distribute reports using 3 different methods. 1. You can send a report via email 2. You can send a report to a FTP location 3. You can publish your file to a drive In older versions of Sage Intelligence Reporting, only… Read more

4 Easy steps using the Advanced Filter to extract unique values in Excel July 5, 2016

Have you ever found yourself in a situation where you have thousands of duplicate values in a column? Perhaps you were importing client data into Microsoft® Excel® from an accounting system or simply combining data in Excel, but often end up having duplicates values in a given column. If you… Read more

2 Easy steps using the TRANSPOSE function in Excel to re-arrange data June 30, 2016

Have you ever needed to change the orientation of a range of cells whilst maintaining links to the source data and end up having to manually recreate the spreadsheet? Here’s a quick and easy way using the Microsoft® Excel® TRANSPOSE function. The TRANSPOSE function returns a vertical range of cells… Read more

5 Easy steps to display positive or negative values in Excel using custom number formatting June 23, 2016

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.… Read more

4 Easy steps to auditing complex formulas in Excel June 16, 2016

When creating formulas in Microsoft® Excel®, the expected result is not always returned, and if a complex formula is being used, it can be time consuming to find where the error is occurring. Luckily there is an easy way to audit complex formulas in Excel. In this tip we explain how… Read more

Learn how easy it is to create a financial dashboard in Excel June 10, 2016

If you are looking for better insight into your businesses financial performance, then creating an interactive dashboard might be the answer. By using a dashboard, you can quickly and easily recognise trends, do comparisons and review performance indicators that will lead to informed decision making. This week we’ll take you… Read more

Learn a better way to visualize your data using the Bing Map app in Excel June 2, 2016

The Bing Map app is a flashy Microsoft® Excel® Add-In app that makes it easy to plot locations and visualize your data in Excel. The Bing Map would be best to use with data where you have to display values for regions or countries. In today’s exercise we will use… Read more

Easy steps to opening an Excel workbook on a specific worksheet May 27, 2016

Assuming you have one frequently used Microsoft® Excel® worksheet in a big workbook, wouldn’t you prefer to land on that specific worksheet when opening the workbook? By creating the simple macro given below, one can land on a specific Excel worksheet and even a specific cell when opening a workbook. Follow these… Read more

Learn how to quickly remove hyperlinks from an Excel worksheet May 19, 2016

Are you looking for an easy and effective method of removing hyperlinks from your worksheet? Sometimes when you copy data from the internet or other sources, you often run the risk of bringing across embedded hyperlinks. Manually removing all the hyperlinks from your worksheets can be a tedious and time consuming… Read more

Protect parts of your Excel worksheet to prevent unwanted changes May 12, 2016

Are there parts of your Microsoft® Excel® worksheet that you wish you could protect? For instance you want to protect certain cells that contain certain formulas and important data, then Worksheet Protection is the answer. Worksheet Protection prevents someone from accidentally or deliberately changing, moving, or deleting important data from… Read more

An easy way to generate PivotTables on separate worksheets in Excel May 6, 2016

You have taken time to create a product sales PivotTable and now you would like to analyze the data. You want to analyze the sales per branch by quickly generating PivotTables for each branch to be placed on separate worksheets. In this week’s tip, we take you through a few easy… Read more

Easily organize data in Excel using multiple data consolidations for PivotTables April 27, 2016

The PivotTable is an effective tool for working with large volumes of data, it allows you to  summarize, organize and view the same data in many different ways quickly and easily. By using the PivotTable multiple consolidation ranges option you can create a PivotTable that consolidates data from more than… Read more

Get creative in Excel using sparklines to show data trends April 21, 2016

New in Microsoft® Excel® 2010, and later versions, a sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values.… Read more

An easy way to create a dual chart in Excel, trending with a secondary axis April 14, 2016

Charts by default display a primary vertical axis when created. This works if there is only one unit of measure in the data. However, should there be different units of measure in your data, a secondary axis will be required, thus allowing you to create a dual chart in your… Read more

Learn how to create a thermometer chart in Excel to show your performance against a set goal April 7, 2016

Did you know that you can create a thermometer chart in Microsoft® Excel®? You can use the thermometer chart to compare your performance against a set goal. For example, revenue vs target or budget vs actual, can be plotted using a thermometer chart. The performance value and its corresponding target… Read more

How to create an Excel combination chart from scratch March 31, 2016

After you have created your Microsoft® Excel® spreadsheet, you can visually represent the worksheet data by creating a chart.  Charts often make your data clearer and easier to understand. If you find that the data is not in sequence or in a group of cells, you may need to create… Read more

Use the DSUM function to add data according to set criteria in Excel March 24, 2016

When analyzing your data, you may want to add certain parts of it up based on set criteria. You are able to do this using the DSUM Function in Microsoft® Excel®. This function enables you to drill down into your data and get specific results, leading to better decision making… Read more

Use the TRIM function in Excel to easily remove spaces between data March 19, 2016

Are you constantly battling with the task of having to manually remove leading spaces between data? Now you can easily remove spaces from your data, except for single spaces between words, using the TRIM function from Microsoft® Excel®. Assuming you have imported data with irregular spacing into Excel an worksheet, in this tip… Read more

Use the MID and FIND functions in Excel to easily link a worksheet to a cell March 19, 2016

Some businesses use Microsoft® Excel® to prepare and present their business reports. Report headings often contain worksheet names, and to make your reports more efficient, you can link these worksheet names to a specific cell. So, instead of retyping the worksheet names in your report headings, you can easily apply the… Read more

5 easy steps to stop PivotTables from returning deleted data in Excel March 19, 2016

In some cases you might find yourself deleting data, and when applying a PivotTable that deleted data may resurface and cause a number of inaccuracies in your PivotTable. In this week’s tip we use 5 easy steps to ensure that when you apply PivotTables to your data, it will automatically… Read more

Use the Sampling Analysis tool in Excel to easily select random numbers. March 19, 2016

This week’s tip is especially handy when running competitions for your business, so please share it with your promotions and marketing teams. The Sampling Analysis Tool is great for when you need to randomly select a number from a given range of values. An example of this is the following;… Read more

Learn how to forecast with scenarios using what-if analysis tools in Excel March 19, 2016

The Scenario Manager is a feature in what-if analysis and you can use this to enhance your forecasting in Microsoft® Excel®. A scenario is a set of values that Excel saves and can automatically substitute in specific cells on a worksheet. You can create and save different groups of values… Read more