Solved

How do I optimize an inherited Excel spreadsheet & chart?

Posted on 2015-01-14
6
199 Views
Last Modified: 2015-01-19
This is my first post so here goes. I've inherited this order tracking project. The data has been extracted out of our GP system and the dates (columns U-Z) tracks the input of certain steps within our inside sales process.  The person that worked on this before created several charts displaying the ‘average number of’ days between these steps. They are trying to show the length of time it takes to get an order through the system to the point where it is now with operations and they can start working on it. I'm wondering if there is a better way to display the charts in these tabs (chart & FP-RP) because I don’t really like the way the charts are laid out. I'm no Excel whiz but these charts don't look right to me, they look like they were thrown together by a third grader. They just don’t look professional.

When I asked the person who worked on this project if they can explain to me the formula used to calculate the number of days their response was they copied it from another spreadsheet they use that tracks number of quotes in our system; and how long it takes to process them through the system. Not really sure if the below formula is what should be used to obtain the information that is needed.

=IF(A5>0,IF(((NETWORKDAYS(C5,D5) )- 1)>0,((NETWORKDAYS(C5,D5) )- 1),0), " ")

Also, when I took a look at the data “import tab” I found numerous errors. The person did a data dump from GP and then started hiding cells and then ran the pivot table. I know the requestor wanted the charts to reflect information for the month of December only. He’s looking for the ‘N’ of orders processed that month based on the ‘order date.’ As well as the time it’s taking each of the 4 steps in the process to be completed based on the completion dates. The individuals enter the dates in the system once they have completed each part of the process.


A couple of things that look wonky to me and frankly I would like to change.
•      Don’t like that the ‘N’ is floating on the top of the chart
•      I think the bar charts look horrible (I’ve also attached the PDF file that they wanted to send out to the smaller team)
•      I know what they are trying to show the smaller team and I find it hard to grasp from the way the chart is laid out
•      Page 4 of the PDF looks HORRIBLE
•      I noticed that column H hasn’t been completed each the inside sales rep and I actually think that fields has the most valuable data. Yes the goal is to find out how long it’s taking to complete certain tasks BUT the real question is WHY is it taking so long. And the “PO Delay Reason” would give us the answer to some of the why’s. What suggestions do you have for capturing this data perhaps on a different chart?

Feel free to use the data that was exported but I cleaned up the data and added a tab ‘cleaned up data’.

HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I honestly don’t even know where to start to make these charts look presentable (sighhhhh).
ORDER-TRACKING-V2.xlsx
ORDER-TRACKING-V2-1-12-15.pdf
0
Comment
Question by:Marcia Morris
  • 3
  • 2
6 Comments
 
LVL 16

Assisted Solution

by:Lucas Bishop
Lucas Bishop earned 325 total points
Comment Utility
You mention a few times that you think the charts look horrible.


•      I think the bar charts look horrible (I’ve also attached the PDF file that they wanted to send out to the smaller team)
•      Page 4 of the PDF looks HORRIBLE

From an aesthetic standpoint, you can change the look fairly easily to a more 'modern' scheme. Simply click the chart and in the top right you'll notice a paint brush. This gives you the ability to apply a theme to the chart. You can also fine tune the layout further by right clicking in most areas (plot area, chart area, etc.) and selecting "Format Chart Area" or "Format Plot Area" for example. This brings up the design components.

If you don't like the style of chart, you can right click on the chart and choose "Change Chart Type".

This are all visual updates though. If you'd prefer the data be presented differently, it may make sense to advise exactly what you'd like to see in the charts (ie. axis x, axis y, etc).


•      I noticed that column H hasn’t been completed each the inside sales rep and I actually think that fields has the most valuable data. Yes the goal is to find out how long it’s taking to complete certain tasks BUT the real question is WHY is it taking so long. And the “PO Delay Reason” would give us the answer to some of the why’s. What suggestions do you have for capturing this data perhaps on a different chart?

This data is in the "Chart" sheet and is included in a chart shown on row 66. There is a table above the chart that includes these reasons. Some of the reasons were intentionally not selected. If you want to include these additional reasons in the existing chart, you can simply click the filter icon in row A56 and check off the un-checked items. This will update the chart below with the missing PO Delay Reasons.
0
 
LVL 12

Accepted Solution

by:
James Elliott earned 175 total points
Comment Utility
This, for me, is an example of graphs for graphs sake. I personally would find this data a great deal more digestible as data, not as pictures. The first 3 pages of the pdf certainly fit into this category and I'd go as far as saying that graphing these reduces the insight you can expect from the data. The 4th dataset is arguably complex enough for a graph, so my recommendation would be to substitute the data tables in place of the first 3 graphs. Then play around with some colour settings for graph 4.

Rgds
0
 

Author Comment

by:Marcia Morris
Comment Utility
I apologize for the gap in inactivity. Had a family emergency and I promised the fam bam I wouldn't 'work' during that time.

James and/or Lucas... do either of you have any input on whether or not the below formula is correct? This is the formula that is used in each of the 'days - XXXX'.

=IF(A5>0,IF(((NETWORKDAYS(C5,D5) )- 1)>0,((NETWORKDAYS(C5,D5) )- 1),0), " ")

As I look at this formula more closely I realize there are no ABS values within the formula so when cells are being added or deleted the formula pulls data from the incorrect cells. For example 'Column A' is referenced in all of the formula's and it shouldn't be used as part of the calculation because it's an 'order number' (example 1). I've included a screenshot for reference.

(NOTE: I am not good at PIVOT tables).

How do I remove all the decimal points from these averages? And is there a way to not have to do it one at a time or is that just not possible (example 2)?

James: to your point, should I replace the charts with a PIVOT table form (example 2).

What I would like to do to your point is present the data in format that is fairly east to digest by others and graph to your point what makes sense.
Example-1and2.docx
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 16

Assisted Solution

by:Lucas Bishop
Lucas Bishop earned 325 total points
Comment Utility
Regarding the formula you referenced from Column U ("Days - Quote to PO Received"):
=IF(A5>0,IF(((NETWORKDAYS(C5,D5) )- 1)>0,((NETWORKDAYS(C5,D5) )- 1),0), " ")

Open in new window


The reference to column A is simply to identify if there is an order number that exists in Column A (ie. if Column A is greater than zero, let's do our calculation). If there is no order number, then Column U is specified to remain blank.

The main formula (NETWORKDAYS(C5,D5) is calculating the difference between Quote Date (Column C) and PO Receipt Date (Column D), using only working days. If the receive date is the same as the quote date, the column shows 0.
0
 
LVL 16

Assisted Solution

by:Lucas Bishop
Lucas Bishop earned 325 total points
Comment Utility

How do I remove all the decimal points from these averages?

Highlight the rows that you want to reduce the decimal point from, and you can click the toolbar icon to remove decimals as seen in the screenshot:
reduce-decimal-places.png
0
 

Author Closing Comment

by:Marcia Morris
Comment Utility
I would like to thank you all for your input. I hope I distributed the points fairly (forgive me if I didn't as this was my first time).
0

Featured Post

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

Join & Write a Comment

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 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

10 Experts available now in Live!

Get 1:1 Help Now