Excel Conditional Formatting with Charts – Two Examples

You may already be familiar with the Conditional Formatting tool in Excel. The amazing tool that improves how we view and visualise our data.

Well unfortunately Excel does not yet have a Conditional Formatting feature that we can apply to charts. I have great news though. There is a way that we can do it, and it is not difficult.

This blog post will look at two examples of Conditional Formatting with charts so that you get a feel for how to do it. You can then apply the same technique to whatever example you need.

Highlight a Column Based on User Selection

In the first example shown in the image below. We want to be able to highlight the column in each chart that corresponds to the product type selected by the user in cell B3.

Excel charts with conditionally highlighted columns

Continue reading

Count Cells by Colour – Excel VBA Function

If you have ever tried to count cells by colour in Excel, you may have noticed that Excel does not contain a function to accomplish this.

We can find out how many cells by filtering the list by colour, and maybe use the AGGREGATE function to return the count. This is an option. But having to filter the list each time may not be good enough.

Because functions such as COUNTIF cannot count by cell colour, we will need to create our own custom function (also known as User Defined Functions or UDF’s) to get the job done. Continue reading

Save Time by Using Project Templates in MS Project

If you find yourself creating projects on a regular basis, you should consider creating your own project template, or customising the default MS Project template. It is easy to do and will save you loads of time in the long run.

This tutorial will begin by showing you what a template consists of, and how to save one. This approach is best if you require multiple templates. For example, specific templates for specific clients or specific templates for specific types of job.

This tutorial will then show you how you can customise the default MS Project template. This will be the best option if you just need one template for all your projects.
Continue reading

Create a Rolling Chart for Last 6 Months

When creating reports in Excel, a common requirement is to report on a rolling basis. For example, this could mean the last 12 months, the last 6 weeks or the last 7 days.

Whatever the timeframe being reported, this can mean a lot of time editing chart sources and formulas to show the right data.

This blog post looks at creating a dynamic rolling chart to show the last 6 months of data, so when new data is added to the table, the chart automatically updates to report the last 6 rows (months).

Rolling chart showing last 6 months sales

Continue reading

Excel Formula to Display the Sheet Name in a Cell

This blog post looks at using an Excel formula to display the sheet name in a cell. By finding the sheet name using an Excel formula, it ensures that if the sheet name is changed, the formula returns the new sheet name.

For the formula we will be using the CELL, MID and FIND functions. Let’s begin by looking at the CELL function.

The CELL function is a fantastic, and relatively unknown, function in Excel. Its purpose is to return information about a cell such as its column, cell colour, whether it is in a protected state or the filename. Continue reading

Euro 2016 Planner – Excel Template

Euro 2016 is here and I have a Euro 2016 planner to share. The spreadsheet has a separate sheet for the group stage and the knockout stage. Enter the results into the boxes on the sheets and everything is calculated automatically.

The group league tables are ranked as the results are entered. As soon as a groups fixtures have been completed, the knockout schedule is created. The quarters, semi-final and final fixtures are then calculated as these results are entered.

The spreadsheet is unlocked so you are free to check out the formulas and how it all works. Download the Euro 2016 planner spreadsheet.

Euro 2016 Planner Template
Continue reading

Export Multiple Sheets to a Single PDF File – Excel VBA

This tutorial will demonstrate how to export multiple sheets of a workbook into a single PDF file using Excel VBA. The code can be seen below and adapted for your own use.

I have previously written a tutorial on exporting all the sheets of a workbook to PDF using VBA. This macro will allow you to be more specific as to what sheets are exported. It will also export to a single PDF file, rather than a separate file for each sheet. Continue reading

Loop Through Subfolders using FileSystemObject

A common Excel VBA requirement is to work with different files and folders of a directory. It may be that you need to search and open a file, list the names of files from a folder, print all the files from a folder, or even delete files.

The different tasks you may need to perform are endless, but fortunately Excel VBA provides an easy way to access and perform operations on the files and folders of a directory. This easy way is known as FileSystemObject.

To use FileSystemObject, otherwise known as FSO, you first need to create an instance of FileSystemObject. You can then access some of the objects from its model such as File and Folder to perform different tasks. Continue reading

Highlight Max and Min Values on a Column Chart

When using column charts to compare values, you may want to highlight the maximum and minimum values on the chart. By highlighting these columns it removes any confusion when trying to view the top and bottom values.

Highlighted max and min values

Finding the Max and Min Values

To show the max and min values on a column chart, we will first need to identify the max and min values of our range. These values will then be used as a second data series when we create the column chart. Continue reading

Email Workbook as an Attachment – Excel VBA Macro

The functionality in Excel to send a workbook via email as an attachment is fantastic. However if you find yourself using this feature often, you may have a desire to automate or speed up the process.

I have been asked this many times in my Excel VBA classes so thought it would be beneficial to share some code to accomplish this.

This Excel VBA Macro will attach the current workbook as an attachment to an email and send it. It will add some text to the subject line and body of the email also.

This code can be adapted to your own needs. I will explain how to do this and the lines you will need to change. There are also a few things to look out for that may cause you problems.

This macro should work for any email service. I have used it with my GMail and my Yahoo accounts successfully. The code may just need some tweaking to work for your own situation. Continue reading