How to Use Microsoft Excel 2010 to Track Percentages of Categories

10,319

5

1

Published

Introduction: How to Use Microsoft Excel 2010 to Track Percentages of Categories

The goal of these instructions is to walk you through step-by-step on how to make your own personal Excel spreadsheet that will allow you to track percentages of different things.

Let's say that you want to make sure that you're getting a healthy balance of protein, carbohydrates, and fats each day; 20% protein, 50% carbohydrates, and 30% fat, for example. What making this spreadsheet will allow you to do, is to input the number of grams of each category, and see what your daily intake of each of these categories are in percentages (shown by a pie chart).

This Excel format could be used to track other things, like how much money you are spending on different types of goods, or how much time is spent on different tasks. Just by making some simple alterations to the steps that will be shown, you can use this Excel format to measure and track just about anything!

Since my original goal for making these instructions was to help people pay attention to what they eat, I will use the example of tracking the amount of protein, carbohydrates, and fat that are in foods eaten.

Step 1: Creating the Title

1. Left-click on cell A1 and begin
2. Type the title of your spreadsheet. For example, you could make it "Daily Food Tracking".
3. Hit the "Enter" key on your keyboard.

Note: Cells in Microsoft Excel are categorized as a specific row and column. Each column is denoted by a letter of the alphabet, while each row is denoted by a number. For example, cell K5 means the cell that is in the K column in the 5th row.

Note: For the duration of these instructions, a left-click action will simply be denoted as the action of clicking, where as a right-click will be denoted as such.

Step 2: Creating the Item Column

1. Click on cell A2
2. In this cell type the over-arching category of the items you're going to track.

Example: If you're tracking you're expenses, this cell would say something like "Item that was bought". If tracking what you eat, it might say "Food eaten".

3. hit "Enter".

Note: You will be putting the name of the item that you "eat" or "buy" in the rows below this cell in the same column. Possible entries might be "Apple" or "Peanut Butter" for food eaten.

Note: You may notice that the text in the cells "bleeding over" into cells to the right. To remedy this, place your mouse in between the row titles "A" and "B" on the boundary of the two boxes. You should see your cursor turn into a vertical line with arrows sticking out the left and right. When this happens, double-click on your mouse; This will expand Column A to be a width that can fit all the text inside.

Step 3: Creating the Category Titles

In this step, you will type in the different categories that you are tracking in the cells that are in Row 2, and in Column B, C, D,.. to however many cells you need.

1. Click on cell B2
2. Type the first category that you're going to track
3. Hit the "Tab" key on your keyboard to move the highlighted cell one unit to the right
4. Type the name of another category that you are going to track.
5. Repeat steps 3 and 4 as needed until you have typed all the categories you wish.

For my example, my categories are "Protein", "Carbohydrate", and "Fat".

Step 4: Creating a Border Around Cells to Be Used

To aesthetically define what cells we will be using, lets create a border around the item and category title cells, as well as the three cells that are below each of these titles.

1. Click and hold on cell A2 (The cell that has your Item Column Title in it)
2. Drag the cursor down to cell D5
3. Release your click on the mouse (Your workbook should look like the 1st picture)
4. Click the downward facing arrow in the Borders tool (This is shown by the blue box and call-out in the 2nd picture).
4. Click on "Thick Box Border" (Shown by a call-out in the 3rd picture).

The cells that you highlighted should now have a thick black border around the outside of them!

Step 5: Creating the Sum Cells

In this step, we will create the cells that sum all the values in one category.

1. Click on cell B6 (The first cell below the border in the B-column).
2. Type in "=SUM(" without the quotation marks (Shown by the call-out in the 1st picture).

Note: the equals sign means that we want to assign a value or a function to this cell. the word 'SUM' is a built in command in Excel that will sum all the values in a determined amount of cells.

3. Click and hold on cell B3
4. Drag the cursor down to cell B5 (At this point, Cells B3 through B5 should have a moving dashed-line around them, as can be seen by the call-out in the 2nd picture).
5. Release the click
6. Press the "Enter" key on the keyboard

Note: If done correctly, the value in the sum cell (Cell B6) should be 0, and the equation in the cell should be

=SUM(B3:B5)

7. Repeat steps 1 through 6 for the C Column (Select cell C6 and sum the cells C3 through C5) and the D Column (Select cell D6 and sum the cells D3 through D5).

Note: The equations for cells C6 and D6 should respectively look like

=SUM(C3:C5)

and

=SUM(D3:D5)

Once completed, your Excel workbook should look like what is shown in the 3rd picture.

Note: You are not limited to only three cells to sum; you can make your border as many rows as you like in the previous step, and sum as many cells as you think you need to track a day of eating, or a month of spending, etc...

Step 6: Inserting a Pie Chart

Now it's time to insert a pie chart.

1. Make sure that you have selected a cell that does not have a value in it (This is because Excel might predict what data you want to make a pie chart out of, and sometimes it is not what is desired by the user).

2. Click on the "Insert" tab that is to the right of the "Home" tab on the top of the Excel window (Seen by call-out in the 1st picture).
3. Click the "Pie" icon within the "Charts" sub-menu (Also seen by call-out in the 1st picture).
4. Click the top left-most option; the regular "Pie" chart. (Called-out in the 2nd picture)
5. Click and drag anywhere in the white chart area so that it does not overlap the cells that you are using (An appropriate place would be how the white chart area is positioned in the 3rd picture).

Step 7: Input Data and Select Data for Pie Chart

Before we create our pie chart, let's input some data.

1. Click cell A3
2. Type the name of the item (For my example, I typed in that I ate "Crackers" under "Food Name"
3. Hit the "Tab" key to highlight cell B3
4. Input the corresponding value for that category

Example: Let's say I eat some "Crackers" with 2.0 grams of protein, 6.0 grams of fat, and 11.0 grams of carbohydrate. In cell A3 I would write "Crackers', in cell B3 I would type in "2", in cell C3 I would type in "11", and in cell D3 I would type in 6. My Excel workbook would then look like the 1st picture. If you were tracking spending, you would put the cost of the item in the category it falls under, and leave other cells blank.

Now it's time to actually create the pie chart.

5. Right-click on the white chart area so that a menu pops up (Shown in 2nd picture)
6. Click on the "Select Data..." option. A window will pop up with the title "Select Data Source" (Window shown in 3rd picture)
7. Click the highlighted "Add" option under "Legend Entries (Series)" A different window will pop up with the title "Edit Series" ("Add" button is called out in 3rd picture; "Edit series" window shown in 4th picture)
8. Highlight the contents In the "Series name" text box, then select cell B2 (The cell with "Protein" written).
9. Highlight the contents in the "Series values" text box, then select cell B6 (The cell that has the SUM function inside it).
10. Click the "OK" button. The "Select Data Source" window will pop back up.
11. Repeat steps 7 through 10 for Carbohydrate (using 'C' instead of 'B' values) and for Fat (using 'D' values)

Note: When these steps are finished, the "Select Data Source" window should look like the 5th picture.

12. Inside the "Select Data Source" window, click the "Switch Row/Column" button.

The pie chart should now look like the 6th picture.

Note: If your pie chart does not look like the picture shown, you may right-click on the pie chart and press the "Delete" key on your keyboard to delete the chart. Proceed with the process again by beginning at sub step 2.

Step 8: Reformat Pie Chart

It's great that we have a pie chart, but we can't seen any percentages!

To change the layout of the pie chart;

1. Make sure that the area that your chart is in is selected (You will know if it is selected if there is a green tab called "Chart Tools" at the top of your window, called-out in the 1st picture)
2. Under "Chart Tools" click on "Design"
3. Click on "Layout 2" above "Chart Layouts" (Also called-out in the 1st picture)

The pie chart should now look like the one in the 2nd picture!

The formatting of your chart is finished! All there is left to do is input more data. No matter the food you put in and the grams of protein/carbohydrates/fats you input, the pie graph will automatically update!

Example: Say I eat some cheese with my crackers, and the cheese has 15 grams of protein, 1 gram of carbohydrate, and 20 grams of fat. If I update my table, my pie chart automatically changes to show the new percentages, which can be seen by the picture in this step.

Although these instructions were for Microsoft Excel 2010, the same end goal could be achieved using previous versions. For a short tutorial on basic tasks in Microsoft Excel 2010, I have attached the following link;

Happy tracking!

Recommendations

• 3D CAM and CNC Class

582 Enrolled

• Oil Contest

We have a be nice policy.