Excel Tutorial – Create and Use Chart templates

Hey Guys! Microsoft Excel is a quite powerful tool and it can help you increase your efficiency by reducing repetitive activities. One such activity is being able to use specific user defined chart templates. While you can create a chart and modify its attributes such as chart type, font size, color scheme etc., doing so multiple times can be taxing if you are dealing with large data . This excel tutorial aims to teach you how to create and save such templates for efficient work.

As a first step in this excel tutorial, let’s create a chart for the following set of data. This snapshot is for Excel 2010. If you have Excel 2013, steps for creating a chart remain the same i.e. Select your data, Go to Insert and select the chart type.

excel 3

Here is our chart:

excel 1

Let’s modify this basic column chart to create something like this:

excel 2

Now that we have created our template, it’s time to save it. For this in Excel 2010, click on the Chart Area -> Chart Tools -> Design -> Save As Template.

excel 5Give a suitable name to your chart template (Chart1 in this case) and make a note of the path where you’ll be saving your template.

Capture 100

Once you are done with this, it is time to create a new chart using your saved template. For this in Excel 2010, again select your data -> Insert and click on Create Chart button as highlighted in red box below.

Capture 106

A menu “Insert Chart” will open. Select “Templates” option from the left bar -> click on your saved template, Chart1 in this case and press OK.

Capture 102

This way you’ll get the chart in your saved template.

In Excel 2013, all the steps are same except the last one. Instead of Save As Template under Chart Tools, Right click on your chart area and select “Save as Template” option.

I hope this excel tutorial helps. You can find such more excel tutorials under the category Excel on my website.

If you found this article useful, share it across your social media platforms. Cheers!

Excel Pivot Table – How to move rows and columns?

Excel Pivot Tables are extremely useful, especially when you are playing with large volumes of data and need to perform data analysis. Often times, we are unable to perform certain tasks in pivot tables due to lack of our understanding of the full functionality of excel pivot tables. One such task is moving rows and columns. Here is a step by step guide to do the same.

First of all, we need a data set as shown below. For a step by step guide to create excel pivot tables logically, read my earlier post on the same. Now that you have created a pivot table, it would show something like this:

Capture 21

Now the real deal.

Move your cursor to cell containing one of your column labels. In our case, the label is ‘FALSE’. Now, right click on the cell and a drop down menu appears. Bring your cursor down to Move option and then select either “Beginning”, “Left”, “Right” or “End” according to where you want your column label to be positioned. That’s it!

Capture 26

A similar exercise on the rows of the excel pivot tables will allow you to re-position rows. Once you have created a pivot table using your data, all you need to do is click on one of the row labels, A being the case in point, here. Then following the same procedure – Right click ->Move option -> “Beginning”, “Up”, “Down” or “End” as per your requirements.

Capture 24

If you find this post useful, do share it on social media.You can like this post using Facebook like button as it appears in left side of my posts. Better still, subscribe to my newsletter and never miss a post. Spread the word. Cheers!

Excel Pivot Table – Create multiple excel pivot tables on same worksheet

In my previous post on excel pivot table, I showed you how to create pivot tables from a data set on your existing worksheet. There are instances when we need to create multiple excel pivot tables using the same data set. Either we can create excel pivots on multiple worksheets using the New Worksheet option while creating excel pivot tables or we can create multiple excel pivot tables on same worksheet.

Referring to our data, we created pivot table like this:

excel pivot-snapshot-1excel pivot-snapshot 36Now, we create one more pivot table from the current data set by again selecting our data set and destination cell as shown below:

excel pivot-Capture 39

excel pivot-Capture 41

So, by doing this, we are able to have two pivot tables on same sheet. You can create more than two pivot tables also, following the same procedure. One caution to exercise, though. Always remember to put empty rows between two pivot tables to avoid overlap of pivot tables and error regarding the same.

excel pivot-Capture 42

So, now you know how to create multiple pivot tables on same sheet. If you liked this post, share it on social media and subscribe to my newsletter. Cheers!

Creating an Excel Pivot Table from your data

excel Pivot tableExcel Pivot Table is an extremely useful tool for arranging your data in rows and columns. It is of great help in data analysis. In this post I am going to show you how to create excel pivot table from your data set.

Creating an Excel Pivot Table from data

Let us consider a data set as shown below. Here, the data in column B with the heading G shows three categories – A, B and C. These can be names of employees, for example. D, E and F represent individual categories – say – question numbers across which A, B and C have given their responses in two choices – TRUE or FALSE.

excel Pivot table

Now, we’ll create an excel pivot table for this data.

For this, firstly select your data as shown below. Don’t miss out on any cell containing data.

excel Pivot tableNow, suppose that you need to create a pivot table for the data in column G across column D or E or F. You need to see how said TRUE and how many said FALSE. You can proceed as below.

Go to Insert -> PivotTable as highlighted in red below. Either directly click on PivotTable or click on the arrow below it and select PivotTable from the drop down menu.

excel Pivot table

excel Pivot table

After you have clicked on PivotTable, a pop up menu opens like this:

excel Pivot table

Table/Range shows your data selection. By default, the destination for pivot table is a new Worksheet. You can either click OK or select destination cell by clicking on Existing Worksheet and then against location as shown below:

excel Pivot table

Next, select a cell in the worksheet where you wish to put your pivot table. In this case, cell selected is G1 as highlighted in red below.

excel Pivot table

A pivot table is created as shown below:

excel Pivot table

Now we start populating fields in this table. For this, just drag and drop the fields you need from “Choose fields to add to report:” to “Report Filter”, “Column Labels”, “Row Labels” and “Values”.

excel Pivot table

We’ll create our pivot table with respect to data in G across D. So, we put G in Row Labels, D in Column Labels and D again  in Values. Remember to put D in Values for showing data pertaining to cells in column with the heading D. And as you can see a pivot table is created.

excel Pivot tableShare this post if you found it useful and subscribe for more such articles. Cheers!