Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# How do I optimize an inherited Excel spreadsheet & chart?

Posted on 2015-01-14
Medium Priority
257 Views
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
Question by:Marcia Morris
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 3
• 2

LVL 18

Assisted Solution

Lucas Bishop earned 1300 total points
ID: 40550682
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

James Elliott earned 700 total points
ID: 40551223
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

ID: 40556562
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

LVL 18

Assisted Solution

Lucas Bishop earned 1300 total points
ID: 40558308
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), " ")
``````

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 18

Assisted Solution

Lucas Bishop earned 1300 total points
ID: 40558324

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

Author Closing Comment

ID: 40559126
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

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month12 days, 4 hours left to enroll

#### 636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.