Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Graphing of attached spreadsheet

Posted on 2014-01-29
9
Medium Priority
?
438 Views
Last Modified: 2014-01-30
Say,

We have a spreadsheet that shows results & grand totals of a few petrol station points.

The export that creates this - puts in a lot of extra blank columns which we do not need.

Please suggest some excel graphs to visually display the table without any extra columns.

Please kindly give details on how to create these graphs because we are not so familiar with excel graphs.

TX
Branch-Totals.xlsx
0
Comment
Question by:shaunwingin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 50
ID: 39817152
Hello,

the model "have data - need chart" does not work.

OK, so you have data. OK, so it may not be in a perfect shape.

But the question is not "How can I force Excel to create a chart (any chart!!) from that data?".

The question is:

What information does your business want to glean about its service stations?

I assume that the numeric values in the data show the "totals due". So, as a CEO or chief of Finance, what do I need to know about the service stations? (You could just go and ask them, actually).

Some examples are:

- which are the service stations with the highest numbers in the current month? In the previous month?

- What are the changes comparing this month to the previous month
- Which 5 service stations had the highest positive change compared to the previous month?
- Which 5 service stations had the highest negative change compared to the previous month?

Can you see what I'm driving at?

It's not about just taking data from a system dump and creating a column or a line chart.

A chart needs to answer a question. But unless you know what the question is, you  cannot build the chart.

42. (click the link)
0
 
LVL 81

Expert Comment

by:byundt
ID: 39819141
teylyn,
That's an inspired use of a blind link.

:-)

Brad
0
 

Author Comment

by:shaunwingin
ID: 39819589
Tx for the insight into graphing....
This would be the basic question:
What are the changes comparing this month to the previous month.

The data by the way is actually the monthly cost of telephone calls by each petrol station.
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 39819650
Sorry, but upon taking a closer look I must say that this data is almost impossible to use as a data source for charting. The merged cells make it hard to select columns.

The first thing to be done is unhide all hidden columns, unmerge all cells and arrange the data in one contiguous table.

After that, we have a starting point for charting.

If this report is created by some software you cannot influence, then you will need to do this every time a new report is created.

In the attached file, I have made the described changes. Then I have added a table. The user specifies a month on Sheet 2. This cell is referenced on Sheet 1 and the values of the selected month and the previous month are put into a table. The difference is calculated in the next column. This difference is then charted in a horizontal bar chart that shows negative values in a different color.

That's just one of many possible ways to present the data in a chart.

cheers, teylyn
Branch-TotalsChart.xlsx
0
 

Author Comment

by:shaunwingin
ID: 39819703
tx! We can change the data - as I now see we have to.

Would it be possible to make a 3D graph - introducing a 3rd axiz. showing the change for each branch over the past months?
0
 

Author Comment

by:shaunwingin
ID: 39819710
I've re-attached your file with data for 14 months. It would be useful to compare the difference for each branch with the cost of the previous year's corresponding month. Can this be done in 3D ie using a 3rd axiz?
Branch-TotalsChart-14-months.xlsx
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 39819872
Hello,

3D charts don't add anything useful to a chart. They are much harder to interpret than 2D charts. Please take a look at Chandoo's pages on dashboards to get some ideas about good charting practices. I'm sure you won't find a single 3D chart on that site.

http://chandoo.org/wp/excel-dashboards/

On that page is a link called "How to Select The Right Chart for your Data"

That should give you an idea about the process.
0
 

Author Comment

by:shaunwingin
ID: 39820117
Tx for feedback.
Can you plot a 2 way comparison showing each branch in a bar graph of current month and previous year same month?
0
 
LVL 50
ID: 39820149
I feel it is time to close this question, because it has been answered.

The topic of data visualization and dynamic charts is exhaustive and will go way beyond what a single question can cover.

If you want to manage your charts, then you will need to learn about how they work.  

I suggest that you get more familiar with the techniques of dashboarding with Excel. The links to Chandoo's site are an excellent starting point.

If you need help applying the principles of dashboards with Excel, please ask a new question.
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question