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/

Restart a Pivot Table in one go

If you want to clear all the fields from a Pivot Table you can use the clear all option.

On the PivotTable Analyze or Analyze tab click the Clear drop down and select Clear All.

 

This removes all the fields and allows you to start again from scratch.

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.

 

 

 

Free Webinar Recording – Excel Format as Table Feature

Feedback score 95% based on 58 responses

In March 2020 I presented a session on Excel’s Format as Table Feature. I covered it’s advantages and how to use it to improve your Excel files.

The detailed pdf manual and example file can be downloaded using the button below. Content is listed below the video.

Download Materials

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

In this session you will learn all about Excel’s formatted tables.

Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.

Formatted tables can allow you to create powerful reports like those in a relational databases.

Topics covered

  • advantages and limitations of formatted tables
  • keyboard shortcuts
  • using formatted tables with formulas
  • solutions to some of the limitations of formatted tables
  • using range names with formatted tables
  • using formatted tables with data validations
  • creating a running total
  • using PivotTables
  • Relationships (Data tab)

As always I will share a few other tips.

Free Webinar Recording – Excel Budget Challenge Solution

Feedback score 92% based on 83 responses

In February 2020 I reviewed my solution to an Excel Budget Challenge from late 2018. This included some advanced Excel budget techniques.

The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video.

Download Materials

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

This session includes a lot of useful content and it brings together a number of different techniques. The content is worthy of a paid session but since it was a public challenge I am presenting the session for free.

The materials includes the challenge documentation, the pdf manual and the solution file. I assume you have read the documentation. You also receive the blank Excel file if you want to create your own solution.

Techniques/topics covered include

  • using INDEX-MATCH (better alternative to VLOOKUP)
  • 3-D formulas to summarise all report sheets and techniques to make using them easy
  • using a reporting template to speed up creation
  • validations
  • extracting sheet names
  • automating reports

Free Webinar Recording – Date and Time Calculations

Feedback score 95% based on 61 responses

In January 2020 I looked at solving some of the frustrations date and time calculations can cause in Excel.

The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video.

Download Materials

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Discover the functions that handle different types of calculations.

See how to avoid some of the frustrations that dates and times can cause. Topics covered include:

  • adding months to a date
  • formulas for the first and last day of the month
  • using today’s date in a formula
  • handling days of the week calculations
  • automating dates in headings
  • how to avoid the traps in time calculations
  • remove times from dates
  • generating random dates (for testing)

As always there will be a few other tips and tricks shared during the session.

Free Webinar Recording – Excel Yourself 2019

Feedback score 93% based on 41 responses

In December 2019 I reviewed four of my articles from 2019 plus some new content.

The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video.

Download Materials

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

This year’s webinar includes
  • a new logic function IFS
  • how to do budget allocations across months using the MOD function
  • how to do complex monthly budget allocations
  • using helper cells to achieve advanced conditional formats

As always there will be other tips and tricks shared along the way.