Simplify data entry for pivot table

  • Hello,

    I am using Excel to manage the sales data and inventory for a restaurant. I know that a database would be a more appropriate tool, but the owner is cheep and that won't happen. The problem I am running into is that what is easy for data entry is not ideal for creating pivot tables. Currently I have most data in cross tabular format.
    date gross sales net sales #of sandwiches gift cards sold
    1/1/1 $2104.04 $1975.33 122 5

    What makes pivot tables most flexible is a single row per variable
    Date Metric value
    1/1/1 gross sales $2104.04
    1/1/1 net sales $1975.33
    1/1/1 # of sandwiches 122
    1/1/1 gift cards sold 5

    obviously the latter is a pain for data entry and the former much simpler.

    Does anyone have any suggestions to make this process simpler? Each day I will need to collect about 50 data points. I also need a flexible solutions as recipes, promotions, and ingredients change frequently.


  • Re: Simplify data entry for pivot table

    This is just the basics but should get you started:

    Another approach would be to add a UserForm front end to generate the original data with an easy data entry. The UserForm can take care of the donkey work creating multiple records for a given date, all the user would have to do is pick a 'Metric', enter a 'Value' and then click 'Update' or simply when both 'Metric' and 'Value' entry fields are complete then update automatically.

    I personally would be inclined to go that route and add a 'Record ID' column as well. If you are going to implement a database in Excel, you are going to want all the tools available.

    As a side comment, I would argue to the owner that developing a database in Excel is likely to prove more costly than investing in a copy of Office that includes Access.

  • Re: Simplify data entry for pivot table

    Hello reillc01,

    While we're glad to have you here at Ozgrid, the new thread page is quite clear about cross posting.
    You cross posted at Excel Forum and did not provide a link per the rules of this forum.

    To understand why cross posting is not tolerated, click the link in my signature and read in full Message to Cross Posters.
    So that you do not incur further infractions of the rules, take time to review them via the link in my signature.


    EDIT: you also cross posted here, as well: MrExcel

  • Re: Simplify data entry for pivot table


    Thanks for the helpful information about cross-posting. I appreciate your taking the time to explain the issue and help me to get my problem solved. You are right that forums are a great tool and I want to respect the time and efforts of the experts who provide their resources out of sheer benevolence. I will be sure to follow the cross posting suggestions in the future.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!