Creating Chart

Resolved · Low Priority · Version 365

Wed 2 Nov 2022, 13:57 Delegate Uday said...

Uday has attended:
Excel Intermediate course

Creating Chart

Hi,

Sorry, this may be a stupid question!

I have a table of three columns:

A= Name (drop down selection)
B= Date
C= Pass or Fail (drop down selection)

I want to convert this table into a chart with stacked columns showing the pass/fail rate of each person each month. How do I create something like this?

Thanks,

Uday

Thu 3 Nov 2022, 15:42 Trainer Martin said...

RE: Creating Chart

Hi Uday,

Thank you for your question to the forum.

When you mention pass/fail rate how is this being measured? Is it the number of passes as a percentage of all tests taken? Or is it some other measure?

Also as each person is taking many tests, the table may need to be summarised as a pivot table in order to visualise the data.

In order to understand fully what you want to achieve I suggest you send an example of this table to

info@stl-training.co.uk

I look forward to hearing from you

Kind Regards
Martin Sutherland
(MOS applications trainer)

Thu 3 Nov 2022, 17:29 Trainer Martin said...

RE: Creating Chart

Hi Uday

I believe I have come up with a solution.

What you need to do is create a Pivot Chart from the table in order to create a summary. This will in turn generate a Pivot Table which can serve a useful source of information. Here's how to do it:

1. Select any cell in the table and go to Insert > PivotChart

2. Select 'Existing Worksheet' then click in the 'Location' field and select a cell in the sheet so the cell is not next to the table. Click OK

3. In the PivotChart panel on the right, put a tick in the 'Name' and 'Date' fields to create Name/Month breakdown

4. Drag the 'Results' field to the 'Legend' box in the panel

5. Drag the 'Results' field again this time to the 'Values' box

6. Finally go to to Design > Change Chart Type and select a 'Stacked' column from the chart options

I hope this has answered your question. Please let me know if it has and if it hasn't then I will try to arrive at an alternative solution

Kind Regards
Martin