Excel - Consolidate

If you have several data sources but the data structure is more or less same. Then you can use "Consolidate" tool to merge multiple data sources into one.

Now imagine you own 3 stores, which sells more or less same items, and you have the quarterly sales data for the last year.

Data of Store 1:

Data of Store 2:

Data of Store 3:

Notice that the column headers are same, however, the row headers are not same across the 3 stores.

1. Open all the 3 workbooks containing the sales data of 3 stores.

2. Open a new workbook. Then Click Data > Consolidate
Note: If data of the 3 stores are in separate worksheets of the same workbook, then you can just work in this Workbook. No need to create a new workbook.

3. Select "Sum" since we want to add up the sales amount

4. In the "Reference" field, go to the workbook and select the data range, then click "Add". Do it for store 1, 2 and 3. Check "Top row" and "Left column". This makes sure that data of all the 3 stores are matched by the row and column headers. If they are not checked, the data will be matched by position only, which will cause error in this example, since the column headers are not identical across all 3 stores.

Note: Check "Create links to source data" will create links to the data source so that when the source data changes, the consolidated data will also update. It also creates an outline.

4. The consolidated data is shown below.

5. If you click the "1" or "2", or "+" or "-", the data table will expand or collapse.

Comments

Excel - Solver examples

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...

Excel - How to count the number of sign changes (from + to - or vice versa) in a column?

Suppose you have a column of numbers and the data can be positive or negative. How do you count the number of times the data changes from positive to negative, or vice versa? We will use three functions, SIGN , INT and SUM to solve the problem. First we add a helper column B, name it "sign", then use SIGN function to get its sign. If the data is positive, it returns 1. If the value is negative, it returns -1. The formula is like: =SIGN(A2) Then we add another helper column C, call it "sign change", which checks whether sign of the current value is different from the last value. INT function is used to return the logical value TRUE or FALSE into 1 or 0. At last, at the end of column C, we use a SUM function to sum all the value changes. In this example, the data sign changes for 4 times (-1 to -3, -3 to 4, 2 to -5, and -5 to 6). VBA method Below is the VBA code to achieve it. You need to add a clickable button on the spreadsheet and assign the following code t...

Excel - Convert between mile, kilometer and meter

Excel has a very useful function convert numbers from one measure unit to another, for example, from Fahrenheit to Celsius, or from mile to kilometer. The function is CONVERT . For example, if we want to convert temperature 1.2 mile to kilometer, how to do it? One way is using the equation: 1 mile = 1.60934 kilometer If you do not know the conversion equation, you can just use the CONVERT function. Since kilometer is not a basic unit in the CONVERT function, you need to convert mile into meter first and then to kilometer. For example: =CONVERT(1.2,"mi","m") will turn mile into meters. If you divided the above number by 1000, you will get kilometer. =CONVERT(1.2,"mi","m")/1000 See the following figure for example. You can also convert kilometer into mile in a similar way. You first need to convert kilometer into meter, then use the CONVERT function. The formula is: =CONVERT(2.35*1000,"m","mi")

Calc Blog

Search This Blog