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 of information, including error values […] Read more →

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 result that you are after, […] Read more →

Have you ever tried to make use of filters in your PivotTable reports, only to find that they don’t stick when you save your template back to the report in the Report Manager?  When your workbook is generated, maybe you’d prefer blank items to be hidden, or you would like it to default to the […] Read more →

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 […] Read more →

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 would be best suited to […] Read more →

Have you ever distributed reports containing PivotTables and found that your recipients aren’t able to interact with them, only having a snapshot of the data? Wouldn’t it be great if they could have some of the same functionality you do, like the ability to filter, drill down, or add other fields that may be of […] Read more →

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 an accountant, you may want […] Read more →

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 =CHOOSE(3,”Apples”,”Bananas”,”Peaches”,”Pineapples”) the function would return […] Read more →

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 MID function returns […] Read more →

In this tip, we continue from where we left off with the previous tip, and will demonstrate how you can use system variables when scheduling reports to run automatically. This will reinforce how they are applied during the scheduling process. If you didn’t catch the previous tip, you can find it here. There are many […] Read more →