Excel - Subtotal

Excel has a convenient data tool to calculate subtotal (and grant total) of data. Suppose we have a data showing number of fruits sold in a store. We want to see how many apples, oranges and bananas are sold today.

Before using the subtotal function, the data MUST be sorted by "item" in this example. Otherwise, it cannot do subtotal correctly.


1. Click Data tab and then select Subtotal


2. Highlight the data you want to do subtotal.


3. A option box pops up. Check each options carefully. For the aggregate function, the most commonly used one is "Sum", but you can also choose "Count", "Average" or others, depending on your needs. Then click OK.


4. Now you see the subtotal and grand total.


5. The "1", "2", and "3" as well as the "+" and "-" are clickable. If we click on "1", we get the grant total. If you click "+", the table is expanded. If you click on "-", the table is collapsed.


If we click on "2", we get a subtotal for each type of fruit.


If we click on "3", we see the grand total, subtotal, as well as all the individual rows.

Comments

Post a Comment

Popular posts from this blog

Excel - Hierarchical chart (Treemap)

Image
If you have hierarchical data, you can create beautiful hierarchical charts. For example, in a university, there are students of different majors in departments, and departments in schools. Suppose we have data below, showing number of students in each school, department and major. In this example, we show you how to make treemap chart. The other type of hierarchical chart is sunburst chart. The data is equivalent if all the cells are filled. I like the following format better. 1. Select the data, then click Insert > Charts > Other Charts > Hierarchical . Select Treemap .  2. A nice-looking treemap chart is shown. The blue area shows all engineering students. The orange area shows all business students. Size of each rectangle is proportional to the number of students in each major.
Read more

Excel - IF function

Image
If function is a logic function. It does two things: Whether the criteria is met Return a value based on whether the criteria is met or not. If the criteria is met, it returns one value; if the criteria is not met, it returns a different value. The reason IF function is confusing for some Excel users is that it does two things: a logical test, AND a return value depending on the logical test result, while the name of "IF" implies it only does a logical test. If this function only returns TRUE or False, it would be much easier to understand. In the following example, there are 5 employees, each having his own budget. Column C records the exact amount of money they spent. If the actual spending is greater than budget, we call it "Over budget", otherwise, we call it "Within budget". The IF function is: =IF(C2>B2,"Over budget","Within budget") The first part, "C2>B2", is a logical test. If this is TRUE, then the IF function ...
Read more

Excel - Solver examples

Image
Excel Solver is used to solve optimization problems, which is like solving equations. In this tutorial, we will give a very simple example on how to use Excel Solver. Basic example Suppose your car is low on gasoline. The gasoline is $3/gallon. You have a budget of $50. How much gasoline can you buy? Of course, for a problem this simple, we do not even need a Solver to solve the problem. However, the purpose is to teach the users how to use Excel Solver, so we make the problem extremely simple. We have the following framework: Goal: maximize quantity of gasoline Relationship: cost=quantity * unit price Constraint: cost<=budget 1. In cell B1, we enter unit price "3", in cell B4, we enter the budget 50. In cell B2, we just enter an arbitrary number as the number of gallons. We entered 5. In cell B3, we enter the formula "=B1*B2" to let it be the cost, which equals to 15. 2. Click Solver under the Data menu. 3. A Solver dialog window pops up. In "Set objective...
Read more