Excel Tutorials

In this short tutorial, you’ll learn how to save a Pivot Table and Chart as a template for later use and how to update this template with new information using the “Refresh” feature within the PivotTable menu.

1. If for example, you have created a great PivotTable and Chart that truly reflects the data relationships that you want to present to management and you will like use the same template but with completely different raw data. You can create a template out of the current PivotTable and Chart and in essence create a shell that can absorb the new data but present it in the same way as the current PivotTable and chart. So first, open the file with the current PivotTable and Chart and go to File>Save As. Next, in “Save as type” go down to Template (*.xlt) and select it. Give it another name, for example, it would be good to include the term “template” in the actual name to help you find it faster in the future.
2. Next, open the workbook containing the new raw data. Select the range with the new data and copy all the data. Then, open the template file, go to the spreadsheet where the original raw data is stored and paste the new data in exactly the same range location. Then click on the PivotTable spreadsheet and click on the “Refresh Data” button and all the data will be refreshed. The row and column headings will remain the same as these were constant and the only thing that will change is the raw data. The PivotTable chart will also be automatically refreshed with all the new data. Finally, you can save this file under a different name.

Great! Now you can appreciate the true value of PivotTables and not fear them any longer.

In this tutorial, you’ll learn how to quickly make unforeseen changes to pivot table data and quickly update that change in the pivot table and Chart.

1. Once you create a pivot table and Chart that works for you, save it and file it in an accessible place. If you have to make a change in the future but don’t want to go through the trouble of creating a new pivot table and Chart, open the file with the original pivot table and Chart.
2. Go to the spreadsheet containing the range of data where you need to make the change. Go to the cell or cells and make the change, then go to the pivot table spreadsheet and within the floating pivot table menu, c+lick on the red exclamation point button – if you place the cursor just over it you’ll see this button is called “Refresh Data.” By clicking on this button, the change within the original data spreadsheet will be automatically refreshed in the Pivot Table and Chart.

Well Done! Always remember the magic of the “Refresh” button when using pivot tables as it will save you lots of time and effort.

In this tutorial, I’ll show you how to create a chart from a Pivot Table.

1. Next, you can create a chart just as quickly as you formatted the table.
2. Go to the floating pivot table menu and click on the button labeled “Chart Wizard” or click on the pivot table drop down menu and select the “Chart Wizard.”
3. By just clicking on the “Chart Wizard” option, Excel creates a chart in a second and you will see it saved in a separate spreadsheet labeled “Chart1” - which you can rename later.

Fantastic! Now you know just how easy it is to create a Pivot Table Chart.

In this tutorial, you’ll learn how to format a pivot table using the feature of “Autoformat.”

1. Now that you have your new table created it’s time to style it and make it look professional and the fastest way to do this is to use the pivot table floating menu bar – you’ll see it somewhere on your spreadsheet area. Click on the first button, labeled “Format Report” or you can click on the pivot table drop down menu and select “Format Report.”
2. Now the “auto format” pop up window appears showing you various report templates that you can use. Check them out, select one and click OK.
3. You will see the table instantly formatted like the auto format template that you just selected.

Great Job! Now that you know how to create a simple pivot table and format it, I’ll show you how to create a pivot table Chart in just a couple of easy steps in the following tutorial.

In this short tutorial, you’ll learn how to create a simple Pivot Table in a couple of steps. With the help of the wizard feature, Excel will actually walk you through the essential steps to build a Pivot Table from scratch.

1. Open the excel file with the table or range containing the data that you want to include in the pivot table.
2. Go to Data>pivot table and Pivot Chart Report and select it.
3. The “Pivot Table and Pivot Chart Wizard” pop up window will appear next. Under “Where is the data that you want to analyze” click on “Microsoft Office Excel list or database.” Next, click the Pivot Table option under “What kind of report do you want to create?” and click Next.
4. Now you need to select the data area that you want to include in the pivot table, so highlight the range of data or the table and click Next.
5. Finally, click on the option to create the new pivot table in a new spreadsheet within the workbook and click Finish.

Now you will see the fields where you are going to actually click and drop the items that you want to include in the pivot table. From the “pivot table Field List” window you can select each item and drag it to the “Drop Items” areas to build the table. This is the step where most of you get frustrated but you just have to start clicking, dragging and dropping until you have the view that actually satisfies your needs – there is no right or wrong way of doing this, you can always click the undo button, so drag and drop until you have the table that meets your specific requirements.

Well Done! Now in the next tutorial, I will show you how to auto format your newly created pivot table in a couple of seconds.

We all have used or will have to use Excel at some point in our lives. Excel is a great tool that makes handling, analyzing, presenting and reporting data a walk in the park. But most people are still deadly afraid of the pivot table feature within Microsoft Excel. The thought of Pivot tables can bring agony to any user and a desperate need to stop considering the possibility of using pivot tables when analyzing of data. It shouldn’t be, because Pivot Tables are not that scary or complicated and once you learn how easy they are to use, you’ll wonder why you chose to waste time doing tiresome and time consuming steps when Pivot tables would have saved you time, energy and money.

We’ll break the main usages of Pivot tables into five mini tutorials so you can learn the most important features of PivotTables in a step by step approach. We’ll cover the following five main sub-topics:

1. How to create a PivotTable from a range or table
2. How to quickly format a PivotTable
3. How to instantly create a PivotTable chart
4. How to quickly add data to an existing PivotTable and chart already saved and styled
5. How to create a template from an existing PivotTable and Chart to use with a new set of data

Since you probably have learned by now how to do a mail merge with Excel and Word, how about I show you how you can use the same principles and create a mailing label mail merge using both Excel and Word. In this tutorial, I will show you how to create a mailing label merge using Excel and Word, so that you can mail out your wonderful created letters from the successful mail merge in the previous tutorial.

1. First, ensure you have the Excel workbook where you have all the address data securely saved and accessible, although, you don’t need to have it open to create the mail merge. Open Microsoft Word 2007 and go to Mailings>Start Mail Merge>Labels, and the “Label Options” pop up window box will appear.
2. Under “Printer Information” select “Page printers” and under “Label information” select “Avery US Letter,” and finally, under “Product number” you need to find the size of the labels you will be using for the mail merge. And click OK. Now before moving on to the next step, go to Table Tools Layout>Table>View Gridlines, so that you can see the gridlines of the mailing labels.
3. Next, go to Mailings>Start Mail Merge>Select Recipients>Use Existing List and the “Select Data Source” pop up window box will appear. Next, find the Excel file that contains the address information for the mailing labels. After you find the file, click on the range or sheet within the workbook that has the table with the address information and click OK.
4. Now looking at the Word document, place the cursor in the first label field (upper left hand corner of the screen) and go to Mailings>Write & Insert Fields>Address Block, and next within the “Insert Address Block” pop up window box click the format that you want to use and hit OK.
5. Next, go to Mailings>Write & Insert Fields>Update Labels, and the format you selected will appear. Following that, go to Mailings>Preview Results>Preview Results and the mailing labels merge will appear for your review.
6. Once you review the document, go to Mailings>Finish>Finish & Merge to finish the process and then you will be ready to print or edit each label section individually.

Good Job! You just created a mailing labels mail merge, and this can really save you time and energy on your next mailing.

Have you ever been asked at work “Hey, do you know how to do a mail merge?” and you think to yourself, I know how to merge columns, yes I have done it but now I can’t remember for the life of me. Perhaps you did it once, or you saw someone do it and thought you would remember, but you don’t. Don’t worry, I will show you how simple and quick it is in this short tutorial. Follow these steps and you will look like a star in front of your peers.

1. First, ensure you have the Excel workbook where you have all the address data securely saved and accessible, although, you don’t need to have it open to create the mail merge. Open Microsoft Word 2007 and go to Mailings>Start Mail Merge>Step by Step Mail Wizard, and the “Step by Step Mail Merge” wizard will open up to the right hand side of your screen.
2. Under “Select document type,” click on “Letters” and hit “Next: Starting Document” to move to next step. Next, click on “create mail merge from the current Word document” and you also have the option to click on “from another existing word document.” And hit “Next: Select Recipients” and click on “Use an Existing List Option” - now we’re going to search for the Excel Workbook with the Address information. Hit Browse and you will see the “Select Data Source” pop window box.
3. Next, look through the options and when you find the Excel file with the address information, click it to select it. Now the “Select Table” pop up window box appears, and in here click on the specific Excel sheet or range that has the address information, and click OK to move to the next step.
4. Now you will see the list of the mail merge addresses. Delete the check marks from any piece of data that you don’t want to include and hit OK. And click “Next: Write your letter” to move to the following step.
5. In this step, go ahead and actually write the standard letter and leave blank the areas that you want to insert from the Excel file – in this case, the addresses data. Once you’re done writing the letter, then look to the right of your screen and you will see five options where you can insert information – the five main options are: Address block, Greeting Line, Electronic Postage, Postal Bar Code and More Items. For the purpose of this tutorial, click on “More Items” so that the “Insert Merge Field” pop up window box appears. In here, click the option you want and hit the “Insert” button.
6. Finally, click on “Next: Preview your letter” to view a sample of the document with the first address from the Excel file. Review it and if it looks OK, click on “Next: Complete the merge.” Your form letters will be created and ready to be printed.

Great Job! It takes several steps but you can see how easy and pain free a mail merge using Excel and Word can be.

If you use Excel a lot, you probably know how to merge cells. However, something you may not use or think much about is merging columns. At some point, you will have a need to merge two or more columns, and you’ll wonder how to do it but figure it’s probably too hard and you find another way to solve your dilema. But, merging columns in Excel is really easy, and in this tutorial, I will show you how to merge columns in a couple of quick steps.

1. First, click on the blank cell where you want to put the new merged data from the different columns. Go to Formulas>Function Library>Insert Function and the “Insert Function” pop up window will appear. Under “Or select a category” select “text” from the drop down menu. And under “Select a function” select “CONCATENATE.” Next, click OK.
2. The “Function Arguments” pop up window box appears. Next, type the cell address of the first column you want to merge (use the first cell of the first column you want to merge) in “Text1” and enter the second column address in “Text2” - again use the first cell address. And click OK.
3. Now you will see the new column with the merged data from different columns created in the location where you had originally specified.

Good Job! So the next time you wonder about merging columns, you’ll remember how easy it really is.

You know how some times you receive raw data in word and you really need to analyze it but you can’t do it in the format that was emailed to you. You have the option of taking this raw data and coping and pasting it onto an Excel spreadsheet, but it will probably dump the pasted data on one column. And you can’t analyze it like that. The solution is to split the raw data into columns. Many people don’t know about this handy trick that lets you split data from column into several other columns. In this short tutorial, I will show you the easy steps to get this done.

1. Lets pretend that you have copied a massive bulk of information from a website. You copied cities, states and university names. But when you copied and pasted the data, all the information got dumped in one column in an Excel spreadsheet. And you want to separate the cities, states and university names into three separate columns. To accomplish this, first, select all the raw data, in this case, the column, and go to Data>Data Tools>Text to Columns, and now you will work with the “Convert Text to Columns” wizard.
2. Next, you need to click on either “Delimited” or “Fixed With” under the heading “Choose the file type that best describes your data” - if you’re dealing with text data with similar formatting, then “Delimited” works better but if you’re dealing with numbers “Fixed With” may work better. And click Next.
3. Now, you need to tell the wizard how to split the data and this will depend on whether you selected “Delimited” or “Fixed With.” If you clicked on “Delimited” you need to specify the symbol that separates the data, in most cases dealing with formatted text is a comma. But if you picked “Fixed With” then you can use the ruler that is provided to split the data by clicking and dropping the check point in the specific areas where you wan the data to be split. Also, you can see a preview of how the data will look like in “Data preview.”
4. Finally, hit the Finish button and click OK. Now you will see the data nicely organized into separate columns. Now you can add headings to each column and format your new table accordingly.

Great Job! Using this highly ignored but powerful function in Excel, you’ll be able to organzie and analyze raw data in no time and look really smart in front of your coworkers.