When combining text from cells in a column, skip the whole 'transpose' thing. Now an important part is, since our data is based on the category and the category is left column in the selection, you need to check the Left column. In Excel 20, combining text from cells in a row works perfectly with the trick described by Chandoo.
Once you have clicked on that, you can see that your formula has been added to all the references. Please click on the Add button so that your formula can be used the next time also. Now click on the reference icon again as follows. Please note that a reference formula has been added to the reference box. Once you click on the reference, please select the data that is not consolidated as follows.
Now click on the reference icon just near to the Browse button. The moment you clicked, a pop-up will open as follows. By default, Excel uses the SUM function to total all the cells in the worksheets that share the same cell references (when you consolidate by position) or use the same labels (when you consolidate by category). Go to Data and click on the tab Consolidate. Most of the time, you will want to total the data that youre consolidating from the various worksheets. So we need to consolidate the items, right? So that I can get an overview of how much money I have spent in each category, so let us do that.Ĭlick on the adjacent cell below “Consolidated Category”, so that you can consolidate the data there. You can see that some of the categories are repeating in that list.
#How to consolidate data in excel 2003 how to
So I thought of sharing how to consolidate the data in Excel, so that it will be more readable. If you want a Step by Step process, with a blank document on the screen, from the Tools menu in Word, select Letters and Mailings and then Mail Merge then in Step 1 of 6, select Letters, then in Step 2, select Use the current document, then click on the Next: Select recipients link, then select the Use an existing list, item and then click on Browse to browse to your data source, then click on. Release the Ctrl button, type the closing parenthesis in the formula bar and press Enter. Press and hold Ctrl and click on each cell you want to concatenate. Type CONCATENATE ( in that cell or in the formula bar. I was just calculating my monthly expenses for the past few months. Here are the detailed steps: Select a cell where you want to enter the formula. To merge cells in excel, first, select the cells which we want to merge together into one cell, and then we can use the short cut key starting from ALT then. Today I came across the requirement to determine the consolidated data in Excel.