• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

How do I optimize an inherited Excel spreadsheet & chart?

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
Marcia Morris
Asked:
Marcia Morris
  • 3
  • 2
4 Solutions
 
Lucas BishopClick TrackerCommented:
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
 
James ElliottCommented:
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
 
Marcia MorrisAuthor Commented:
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
Independent Software Vendors: 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!

 
Lucas BishopClick TrackerCommented:
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
 
Lucas BishopClick TrackerCommented:

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
 
Marcia MorrisAuthor Commented:
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

Industry Leaders: 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!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now