Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Graphing of attached spreadsheet

Posted on 2014-01-29
9
Medium Priority
?
441 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
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

782 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