Solved

Graphing of attached spreadsheet

Posted on 2014-01-29
9
414 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:teylyn
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 80

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
 
LVL 50

Accepted Solution

by:
teylyn 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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:teylyn
teylyn 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:teylyn
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now