Percentage Area Chart in Excel

Conditional Format technique

You can use a pie chart to display a percentage, but it wastes a lot of space. An alternative that takes up less space is an area chart.

Below are some images from the Australian Bureau of Meteorology website. They display a percentage likelihood of rain including a small chart, for two separate days.

http://www.bom.gov.au/wa/forecasts/perth.shtml

This is not a standard Excel chart type and is hard to create as a chart. It is straightforward to create with Conditional Formatting.

If you want a horizontal or vertical progress chart I have written about them previously at the link below.

https://a4accounting.com.au/horizontal-or-vertical-progress-bar-in-excel/

Let’s look at the layout we need to create a chart like this above.

We need to create a matrix of 20 cells, 10 columns across two rows.

Each square represents 5% or 1/20th  of the whole.

Note: this chart cannot display values over 100%.

We enter a value to display in cell B2. This is a merged cell. I rarely use these but because I need the effect of going across two rows I have to use it.

Cells C2 and C3 have their values input. Cell D2 and D3 have a formula that adds o.1 to the value from column C on the same row. That formula is copied all the way across to column L.

We don’t need to see the values, but we need them there for the conditional format to work.

We now need to modify the range to have square cells. I will make the cells 15 pixels wide and high.

We can use a custom number format to hide the values. Select the range C2:L3 and press Ctrl + 1 this opens the Format Cells dialog.

In the Number tab select the last option on the right – Custom.

In the Type box enter ;;; that’s three semi-colons in a row and then click OK.

This format stops the values in the range from displaying or printing.

We now need to apply the conditional format to the C2:L3 range.

Click the Conditional Format icon drop down on the Home ribbon and select New Rule.

In the dialog that opens select the second option in the top section and select “less than or equal to” in the second drop in the middle section.

Click in the box on the end and then click cell B2 on the sheet. Use the Format button and click the Fill tab and choose a colour – I chose blue and click OK. It should look the image below. Click OK to apply the format.

The chart displays.

We need to add gridlines to the chart area. I will use blue lines to match the colour of the chart.

With the range still selected. Press Ctrl + 1 and click the Border tab.

I have used the Inside and Outside Presets and changed the Color option to blue.

Job done – I removed the sheet gridlines and also added a border to the merged cell B2. A couple of examples below.

This can be useful for dashboards where space is at a premium. The downside is having to change the column widths and cell heights. To get around that issue consider using Paste Picture Link – see the link below.

https://a4accounting.com.au/flexible-dashboard-technique-for-reports/

Linking text boxes

A trick to allow copying between sheets

Linking to a text box in a sheet is straightforward, unless you want to copy that linked text box to another sheet and retain the link. Here is how you do it.

Standard Technique

To link to a text box you click the text box and then click in the Formula Bar and press = and then click the cell to link to and press Enter.

This works OK on the sheet but if you copy the text box to another sheet it links to the same cell in the other sheet. If that’s what you want, great. If it isn’t then you need to use this technique.

Text box copy technique

Click the text box click in the Formula Bar and press = then instead of clicking on the current sheet click on another sheet tab and click a cell in another sheet then return to the current sheet and then click the cell you actually want to link to and press Enter.

By doing it this way the sheet name is included in the link and that ensures the link is kept when you copy the text box to another sheet.

You could also manually type the sheet name into the Formula Bar, but using the mouse is much easier.

Fix dd.mm.yy date format

On a recent Webinar I was asked a question about an unusual date structure that was imported. The structure dd.mm.yy was not recognised by Excel as a date. Here is formula that fixes it.

Below is an example of the date issue.

The formula in cell B2 is

=SUBSTITUTE(A2,".","/")*1

As you can see the dates in column A are left aligned. That is a clue that they are not recognised as dates in Excel. Dates are right aligned.

The SUBSTITUTE function replaces the full stop between the numerals with a / and makes it look like a date.

This isn’t sufficient as the SUBSTITUTE function will return text. The *1 at the end converts the text date in to a real date that Excel recognises.

Note: Power Query can also automatically fix dates like these when it imports data.