Solved

Graphing of attached spreadsheet

Posted on 2014-01-29
9
422 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
  • 4
  • 4
9 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst 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
Ingeborg Hawighorst 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

Expert Comment

by:Ingeborg Hawighorst
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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.

820 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