Solved

Graphing of attached spreadsheet

Posted on 2014-01-29
9
425 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
SendBlaster Pro 4 - Bulk Email Sending Software

SendBlaster 4 Pro - Best Bulk Emailing Sending Software
Automatic Subscribe / Unsubscribe Processing
Great for Newsletters & Mass Mailings
Optional HTML & Text Composition
Integration with Google Features
Built in Spam Score Checking
Free Professional Templates - Feature Packed!

 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 500 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 500 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

Scamming the Scammers!

Have you ever heard of Scam Baiting?
It's a highly entertaining sport that you can participate in.
Introduction to beating scammers at their own game and how you can help
Share your thoughts, ideas and experiences on the topic.
Links to top Anti-Scam resources provided.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

751 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