I have the luxury of designing the data from which to create reports and a dashboard, so I want to be as efficient and flexible as possible. My question is whether it is better to track attributes in a few columns with different terms in each column (for example "Client Type" being "New" or "Existing") or in many columns with an "X" under each to indicate an attribute (for example, separate columns for New and Existing clients with an X in each row to indicate the type for each project). I've attached a workbook with samples of each in tabs labeled Columns and Multi. Or is some other arrangement preferable?
Background:
I write proposals for a living, in MS Word. Excel is a foreign language to me. Our existing spreadsheet, created by us Word people with some help from our pricing team, tracks our proposals and assignments. We've maintained three separate sheets to track proposals for new prospects, existing clients, and miscellaneous projects. Our clunky dashboard is difficult to maintain and not flexible. My research indicates I should rebuild the database to put all data in a single sheet, make it a table, and use Pivot Tables to create dashboards and any reports. I am just learning what a Pivot Table is (marvelous invention!) and figuring out how to build a dashboard to show my department's productivity--and our need for additional staff to keep up with our growing workload.
In the same workbook we assign projects. A separate sheet has an extended calendar to show each writer's current workload (the Assignments tab in the attached). My plan is to copy the information from that sheet into the data table when each proposal is complete, to maintain an accurate tally.
I would love any suggestions from you Excel experts on how to do this more efficiently!
Thank you,
--Bthis
Background:
I write proposals for a living, in MS Word. Excel is a foreign language to me. Our existing spreadsheet, created by us Word people with some help from our pricing team, tracks our proposals and assignments. We've maintained three separate sheets to track proposals for new prospects, existing clients, and miscellaneous projects. Our clunky dashboard is difficult to maintain and not flexible. My research indicates I should rebuild the database to put all data in a single sheet, make it a table, and use Pivot Tables to create dashboards and any reports. I am just learning what a Pivot Table is (marvelous invention!) and figuring out how to build a dashboard to show my department's productivity--and our need for additional staff to keep up with our growing workload.
In the same workbook we assign projects. A separate sheet has an extended calendar to show each writer's current workload (the Assignments tab in the attached). My plan is to copy the information from that sheet into the data table when each proposal is complete, to maintain an accurate tally.
I would love any suggestions from you Excel experts on how to do this more efficiently!
Thank you,
--Bthis
