Excel - automating the creation of a particular graph

Question

I am trying to figure out a way to create this graph automatically on Excel and link it to PowerPoint so that when the data in Excel gets refreshed, the PowerPoint chart gets updated as well. Currently, the underlying chart in this photo is a 100% stacked column chart with gap width set to 0 so that there's no space between the bars for different years. However, I have had to manually place the rectangles on top of the stacked column chart and adjust the height and width so that it's spatially accurate (for example, 16% is 16% of the area of the rectangle below). As you can imagine, it's a very long and frustrating process to update the rectangles manually when the underlying data gets updated. Is there a way to automate the creation of such a chart in excel?

I was thinking of creating a Marimekko chart in Excel (which requires manipulations for the data to be in the format desired but can be done) for, let's say, FY18 and rotating it by 90 degrees clockwise. But I'm not sure how to rotate the chart without saving it as an image first. Also, by this method, I'll have to create Marimekko charts for each of the financial years (18, 19, 20 and 21) individually and then place them side by side which might reduce some labor as compared to the method I am currently using but will not be immensely helpful.

Another method that I have tried without much success is overlaying the rectangles in the form of a bar graph for each year. See example below.

The problem with this being that I have not been able to figure out a way to adjust the width between different bars in excel so that they overlap exactly with the underlying chart to create the desired view.

Any thoughts on a solution to this problem will be much appreciated. I have been wrapping my head around this for a while now but haven't been able to make much headway.

Answer 1

I don't have specific instructions, but I can give you a high level outline and links to the source material I picked up the ideas from.

#1 define your source data as an Excel Table
#2 create a PivotTable
#3 Use dashboard techniques to create the automated chart
#4 Copy the chart, Paste with Link in PowerPoint.

.  * You can add new data to the source data table. 
.  * When you refresh the Pivot table the new data gets "sucked in" and processed
.  * The updated data shows up in the chart automatically
.  * All you have to do is refresh the chart in the PPT to pick up the new chart from Excel.

It all sounds simple, but this is a very high level. 

Here is a collection of links to recordings of several free about creating pivot tables, using them to create pivot charts (with automated label update feature), combining charts into a Dashboard. With some sections on using "get and transform" to import and manipulate data to export to the pivot tables.

It will take you a day or two to work through these recordings, but by the end you will have had a glimpse at a lot of useful features, including everything I think you need to do to automate your PPT chart.

myonlinetraininghub.com-  Mynda Treacy  
http://www.myonlinetraininghub.com/excel-webinars 

webinar 1-  Creating Excel Dashboards Webinar 62min -  
How to build an interactive Excel Dashboard that you can update in less than one minute

Webinar 2- Dashboards With Power Query & PowerPivot  53min   
how to use Power Query to get data from multiple sources, filter and clean it, send it to PowerPivot and create relationships between data tables.
Then I'll mash it up in PivotTables to create this interactive Excel Dashboard.

Webinar 3- Mastering Pivot Tables 64min
Set up your data and create a Pivot Table in less than 3 minutes which will increase your efficiency. 
Put your key business metrics into an interactive Pivot Table
Create an interactive Dashboard using Slicers & Pivot Charts that will amaze your boss

All three webinars have downloadable workbooks

Pivot Tables Intro from ExcelJet  Free Videos
Consists of 3 short videos with examples 
#1 Why Pivot Tables  10 min  https://exceljet.net/plc/why-pivot-tables 
   One of the best ways to see why pivot tables are worth your time is to compare building a pivot table report to the process of building the same report manually. 

#2 What Are Pivot Tables Good For – 3 Examples: Voting, Sales Reports, Instrument Reading- 9 min  https://exceljet.net/plc/what-are-pivot-tables-good-for 
   In this video, we shift from why to what, and we explore some ways you can use pivot table with different kinds of data. In particular, we look at how pivot tables help you answer questions about data...even ugly transactional data that no one else wants to touch.

#3 10 pivot table problems and easy fixes – 11 min https://exceljet.net/plc/10-pivot-table-problems-and-easy-fixes 
  1.	New data not included when you refresh
  2.	You want totals and percent of total
  3.	You don't want a data sheet
  4.	You don't like the automatic field names
  5.	Your number formatting gets lost
  6.	You want to see the data behind a subtotal
  7.	Grouping one pivot table affects another
  8.	Refreshing a pivot messes up column widths
  9.	Field headings make no sense and add clutter
  10.	There are blanks in your pivot table

Excel.TV Learn to Build Excel Models that Work (80 min total, approx. 60min demonstration, rest intro and course shilling)  (free webinar replay)
https://excel.tv/members/webinar-signup-building-an-awesome-data-model-in-7-easy-steps/ - this page shows when they are offering the live webinar
 https://excel.tv/members/webinar-data-modelling-excel-online-course/  - This page gives you the webinar recording and associated file download
Tables, Pivot tables, data modelling, PivotCharts, dashboards
PLAN, DESIGN, and MAINTAIN Excel models that save your company money, increase efficiency, and solve real business problems.

@ 04- How To Build 3 Excel Dashboards & Reports in Under 90 Minutes ...   https://excel.tv/members/ev-how-to-build-3-dashboards-reports-with-data-vis-in-under-90-mins/ 
This link takes you to a periodic replay of the webinar
Learn the secrets to building interactive Excel dashboards and awesome info graphics in 90 minutes. 

@ 05- ExcelCampus Intro to Pivot Tables in 3 parts  – Jon Acampora  https://www.excelcampus.com/ 
Go to this page and sign up for the newsletter to be notified when webinar is offered again

Part 1: Intro - 
https://www.excelcampus.com/charts/pivot-tables-dashboards-part-1/  15min Free Video
Andy has been tasked by his boss to create some charts and a dashboard for an upcoming presentation.  He really wants to impress his boss, but doesn’t have time to learn complex formulas or macros.  What he really needs it Pivot Tables and Pivot Charts.  
Pivot Tables allow you to quickly summarize your data.
Intro To Pivot Tables Part 1.xlsx (76.5 KB) 

Part 2: Answer Questions about Your Data 15min  Free Video
https://www.excelcampus.com/charts/pivot-tables-dashboards-part-2/ 
some of the questions we answer in this video:
    “What are the top 10 product categories?”
    “What is the average unit price for each category?”
    “How many orders did we have for each category?”
    “Who are the sales reps selling in each category?”
    “Which categories make up over 50% of our total revenue?”
Update Pivot Tables with More Data
Intro To Pivot Tables Part 2.xlsx (101.5 KB) 

Part 3: Building the Dashboard 15min  Free Video
https://www.excelcampus.com/charts/pivot-tables-dashboards-part-3/ 
In this video we finally help Andy put the dashboard together.  This dashboard will help communicate our findings and tell a story about the performance of our business.  
The great part about using pivot tables and pivot charts is that they are very easy to update.  When next month rolls around, we simply paste the new data to the bottom of our source data sheet, update the source range, and hit the Refresh button.  It will only take a few minutes a month to maintain. 
This video is packed with tips for working with Pivot Tables and Pivot Charts.  Here are some of the topics covered.
    The Report Filters area explained.
    Group dates into months and years to create a summary trend report and chart.
    Group amounts to create a distribution chart (histogram).  
    Resize all charts to be the same size.
    Prevent charts from resizing when column widths and row heights are changed.
    Add slicers to make the dashboard interactive.
Intro To Pivot Tables And Dashboards - Part 3.xlsx (160.5 KB) 

@ 06 MyExcelOnline 2 Pivot table webinars by John Michaloudis and Oz Du Soleil
https://www.myexcelonline.com/free-excel-webinars   (closed now, but you can register for email when the webinars are offered again.

Free Power Query & Data Cleansing Webinar   118min  
  *  The most powerful feature in Excel since VLOOKUP...it's called POWER QUERY (GET & TRANSFORM) & it will save you HOURS each day!!! 
  *  Transform messy data & automate your daily/weekly/monthly reports within minutes (NOT DAYS!)... AND without using VBA or Macros! 
  *  Data Cleansing Excel Tips & Special Bonus attendee material that you can keep and use to become better at Excel straight away, skyrocketing your personal & professional development! 
  *  Plus learn how to use Data Models, Unpivot, Parse Text, Consolidate Reports & Flash Fill!
  *  Data Models- Analyzing multiple datasets as if they are a single dataset
  *  Parsing Text- Splitting data apart, ie when name aage and email are all in same cell
  *  Inner Join – Comparing lists to extract data that appears on both lists
  *  Unpivot – convert a report into raw data so that I is useful for analysis
  *  Consolidate multiple worksheets 

Free Pivot Table Webinar
During This Free Excel Webinar You Will Learn …
  *  How to set up your data and create a Pivot Table in less than 3 minutes which will increase your efficiency!
  *  How to put your key business metrics like Year to Date Sales, Monthly Variations and Top 10 Customers in an interactive Pivot Table, taking your analytical skills to another level!
  *  How to create an Interactive Dashboard with Slicers & Pivot Charts that will WOW your boss and get you noticed by top Management!
  *  Pivot Table tips & special Bonus attendee material that you can keep and use to become better at Excel straight away which will skyrocket your personal development!

As well, go to the website, https://www.myexcelonline.com/blog/contact/  , and search for “The Ultimate Excel Resource Guide”. It is a free 80+ page PDF download of links to Excel blogs, forums, courses, books and Add-ins.  Great place to start looking for help

.
...........********************************...........
**************** BS Disclaimer **************
...........********************************...........

Standard boilerplate legalistic bafflegab disclaimer for any links to non-Microsoft websites:

The link(s) to non MS-page(s) appears to be providing accurate, safe information. I suggest you stick with the “manual” instructions. Unless I explicitly suggest it, avoid downloading their “utilities”.  Thoroughly research any product advertised on the site before you decide to download and install it. Sometimes they include 3rd party programs, "unwanted" programs that are automatically installed during a standard install.  So, if offered, do a "custom" install and carefully read each step. One of the steps MAY give you an option to install, or NOT, "additional" programs.  99.9% of the time you don't want to let them be installed.  For example, when you install Adobe Player you are given the option of installing 2 additional "free trial" programs: Potentially Unwanted Programs: MacAfee  Security Scan and MacAfee Safe Connect.  The option to install is turned on, you have to manually turn it off. Those are PUP's.  If you click "OK"/"Next" without paying attention you get the 2 "extra" programs. 

As well, watch out for ads on the site. They may advertise products that include PUPs. Thoroughly research any product advertised on the site before you decide to download and install it.

............**********************************...........
*************** End Disclaimer *****************
............**********************************...........
.

Answer 2

Hi Rohn,

Thank you for your response. The issue that I'm facing is quite specific. I am familiar with the solutions you outlined in your answer (for example, creating dashboards with pivot tables) but that hasn't been helpful to solve the issue at hand.