Authentic voices. Remarkable stories. AOL On Originals showcase the passions that make the world a more interesting place.
Go behind the scenes with some of the biggest digital celebrities to see what life is like when the blogging and tweeting stops.
The story of punk rock singer Laura Jane Grace of Against Me! who came out as a woman in 2012, and other members of the trans community whose experiences are woefully underrepresented and misunderstood in the media.
Documentary shorts conceived of and directed by famous actors. Jeff Garlin, Katie Holmes, Alia Shawkat, Judy Greer, and James Purefoy
Park Bench is a new kind of "talking show" straight from the mind of born and bred New Yorker and host, Steve Buscemi.
Digital influencer Justine Ezarik (iJustine) is back. After covering the world of wearable tech last season, iJustine is expanding her coverage this year by profiling the hottest tech trends across the country.
A 12 episode documentary series following 5 startup companies competing in the 2013 San Francisco TechCrunch Disrupt Startup Battlefield as they fine tune their products and eventually present in front of a panel of judges in hopes of winning $50,000 in funding.
Enter the graceful but competitive world of ballet through the eyes of executive producer, Sarah Jessica Parker. This behind-the-scenes docudrama reveals what it takes to perform on the ultimate stage, the New York City Ballet. Catch NYCB on stage at Lincoln Center.
Nicole Richie brings her unfiltered sense of humor and unique perspective to life in a new series based on her irreverent twitter feed. The show follows the outspoken celebrity as she shares her perspective on style, parenting, relationships and her journey to adulthood.
Explore what it means to be human as we rush head first into the future through the eyes, creativity, and mind of Tiffany Shlain, acclaimed filmmaker and speaker, founder of The Webby Awards, mother, constant pusher of boundaries and one of Newsweek’s “women shaping the 21st Century.”
Gwyneth Paltrow and Tracy Anderson spend time with women who've overcome hardship, injury, and setbacks to triumph in the face of adversity.
Hank Azaria’s touching, humorous, and often enlightening journey from a man who is not even sure he wants to have kids, to a father going through the joys, trials and tribulations of being a dad.
ACTING DISRUPTIVE takes viewers inside the businesses and passion projects of Hollywood’s top celebrities.
Learn how to use the consolidate function in MS Excel.
Tags:data consolidation,excel advance training,function,microsoft excel 2007,microsoft windows,total training
Grab video code:
The next thing we are going to talk about is Data Consolidation. So, on the File Menu, let us open the second file. On the Desktop, we will choose Project Files, Lesson 1 and then the Consolidation file. Our data consolidation is not as cool as the advanced subtotals function. It has actually been around forever. It is very good in a couple of limited uses. In this case, we have Account Number in column A, Customer Number in column B, Quantity, Revenue, Cost of Goods Sold and Profit. The Consolidation Command is great for summarizing data by exactly one field. So I can choose to summarize the data, let us say by customer and automatically get totals for Quantity, Revenue, Cost of Goods Sold and Profit.
The consolidation command is located on the data ribbon in the data tools group. In order to use the command, you have to start in a blank cell. It has plenty of space both below and to the right. So I will choose cell H1 and go to the consolidate command. Now, in the consolidate dialog box, we have to specify our reference. The most important part about this reference is that the left most column has to be the data that we are attempting to be summarized by. Since I am trying to create a summary by customer, I will start in cell B1 and use the End and Down Arrow key and End – Right Arrow key while holding down the Shift key to select all the cells in my data set. You will see that Excel records the reference as B1 to F564. Now the important part here is we need to check the two boxes in the lower left hand corner, Use Labels in Top Row and Left Column. That tells Excel that rather than summarizing all the data, we want one row for every value in the left most column and one column for every value in the first row. Click Okay and I will hit the Down Arrow here so we can see the result.
There are a couple of frustrating things with the consolidate command. The first thing that I do not like is that they do not put the heading in above the left most column. I really would like to have the customer heading copied to cell H1. The other thing that is a little frustrating is that the results set is not sorted in any particular manner. The customers appear exactly as they originally appeared in column B. To solve this, we want to add a customer heading in H1 and then use the A to Z button in the data ribbon in order to sort our data. There we go. We now have a very small data set that shows every customer exactly once in the Quantity, Revenue, Cot of Goods Sold and Profit. Now, this was a very simple use of the consolidation. Sometimes, we have a more complicated situation. Like for example, we wanted to consolidate for every account number. If we consolidated based on columns A through F, there is a column right in the middle that is not numeric. Excel has no clued what to do when we asked it to summarize the customer field and so we are going to get blanks. Let me remove this consolidation area and we will try and consolidate again. This time, consolidating from column A through column F.
I will delete those columns and again, I want to select a single cell in my output range. Choose the consolidate command from the data ribbon. The second time that you do consolidation in the same Excel section, Excel remembers the previous reference. And that is a real problem if you are trying to do a different reference. The best choice is to delete the first reference and enter a new reference. So, click the old reference, click the Delete button and now, it is as if we started the consolidate command from scratch. Click in the Reference field and again, I want to start this time in cell A1, hold down the Shift key, use End+Down and then End+Right. Click the Add button, click the Okay button and the Down Arrow key and you will see that instead of summarizing by customer, we summarize by account number. The frustrating thing again, there is no Account Number heading in H1. Let us add that. We also have no Customer Names in column I.
Now, if you are familiar with the VLOOKUP function, and we will talk about the VLOOKUP later in the series, you can v