This article is designed to provide you with guidance on creating Pivot Tables in Excel. It uses the Student Analytic Data export for a section as an example, to show how you can take the sometimes VERY large data files and create "views" of the data that you find most useful.

The information given here is provided as a GUIDE to this Microsoft Excel feature, and specifically for the EchoVideo data made available through the platform. For more information or other steps, refer to the Excel documentation.

What are Pivot Tables

Pivot tables are essentially "data views" where you identify which data items you want for the rows, and which data items you want for the columns, then select the values you want for each row-column intersection. You can change these as needed to view different values from the same exported data-set. 

In our example, we are using Pivot tables to give us at-a-glance listings of student engagement metrics from a course section. The metric we want to see is used as the value, with one row for each student and one column for each class. Once set up, you can add or exchange metric values very easily, to show you other data for each student and each class.

First things first. Generate a CSV Export from EchoVideo and open it in Excel. At this point you may want to save the .CSV file as an .XSLX file.  Pivot tables generate new worksheets in the file (the original data sheet is unchanged), and CSV file types can only have a single worksheet. You don't HAVE to do this until you're done creating your pivot table, but doing it first ensures you can just click the Save icon if you get your pivot table(s) configured the way you want.

From there, follow the below instructions to create a pivot table. You can create as many pivot tables as you want. Each new pivot table lives in a different tab/worksheet of the single XLSX file. OR you can create ONE pivot table, then adjust which data it uses. 

Creating a Pivot Table

Once again, the below procedure uses a CSV export from the Section Analytics Students page, but you can apply these steps to any exported data set.

SPECIFICALLY the below instructions create a pivot table view where each Student occupies one row, each class occupies one column, and the data values shown are for "video view %". 

To create a pivot table for student analytic data

1. With your data export open in Excel, select all the data in the sheet. There are two methods for this:
    - Ctrl + A (for Windows) or Cmd + A (for Mac)
    - Click the TOP LEFT corner of the sheet, as shown below.
   
2. Click Insert from the main menu, then click Pivot Table from the left side of the Insert ribbon. as shown below.
   
   If you do not see Pivot Table as an option , click Tables then click Pivot Table from the options provided as shown below.
   
3. In the Create a Pivot Table box that appears, you can accept the defaults (because you've already selected the data set you want to use) and click OK. 
   
   

   Excel changes to show you the new worksheet containing your new pivot table in the main panel, and a Pivot Table options panel on the right side of the sheet.

4. Drag and drop fields from the list into the locations below the list as follows:
   
   • Drag Student Name from the list box into the Rows box.
   • Drag Class and/or Lesson Date from the list box into the Columns box (see note below)
   • Drag Video View % from the list box into the Values box.

   If your classes all have different dates, you can simply use Lesson Date for your columns. If your classes all have different names, you can simply use Class. If you have some classes that have the same date but different names (or vice-versa), you will want to use Class in addition to Lesson Date to distinguish them. Try both or either method to see what your table looks like, and drag fields in and out (and reorder them in the box) until it looks the way you want.

   
   

   The above figure shows an initially completed Pivot Table form with the table showing the configured data view on the left.
   Notice in the above figure that the Values box shows "sum of video view %". "Sum of" is the default setting for whatever metric you drag into that box. You don't HAVE to change it, but you may find it useful to change it to Average instead of Sum due to the kinds of data these exports contain.

5. Click next to the Video View % entry in the Values box and select Value Field Settings as shown below.
   
6. In the Value Field Settings dialog box, select Average from the list. Even if you are NOT working with percentages, a "sum" of the analytic data doesn't make much sense; it is more likely you will want "an average across classes" or similar information for each metric.
   
7. Select Number Format from the bottom of the dialog box, identified in the above figure. This step is optional but ensures your data is shown in a 2 decimal place form.
8. Select Accounting from the list, as shown in the below figure. DO NOT SELECT PERCENTAGE. Using percentage will attempt to "re-percentagize" the values.
9. Select None from the symbol drop-down list (the default for Accounting type is a dollar sign).
   
10. Click OK to close the Format cells dialog box.
11. Click OK to close the Value Field Settings dialog box.
    At this point, your view of Excel should look something like the following figure.
    
12. Close the Pivot Table fields panel using the X in the top right of the panel.
13. Click the arrow next to Column Labels as shown below.
    
14. REMOVE the check from the N/A entry in the list. N/A is the "section rollup" across classes for the original data-sheet, and that field will have no purpose in this pivot table.
    Incidentally, this is also the way you can remove classes from the table whose data you don't need to see right now. You can always re-check them when you want them back.
15. Click the arrow next to Row Labels as shown below.
16. Remove the check for any students you don't want to see (e.g., students who have dropped the course or who have never interacted with the class materials). In the below figure, the intent is to remove (uncheck) Matthew Gallo from my table because he has no data to view because he never attended the course. 
    

When finished, you should have a new worksheet in your Excel file for the Pivot table and it should look something like the following figure. If you like it, SAVE your file. 

The metric you are using for the values field appears in the top left, so you can easily see what data is in this table. You may also want to rename the sheet to identify what information this pivot table tab shows. To rename the sheet, right-click on the tab and select Rename from the menu.

This SHOULD be enough to get you started with using Excel pivot tables for analyzing your data. But if you want more, keep reading! There are a few more tips and tricks for you to try when you're ready.

Once you have it set up, you can re-use this table by copying the file and replacing the original export data. OR you can add a new worksheet to the file and paste in updated export data. All of these tips and more are provided in the sections below.

Pivot Table Extras

This section is to provide additional navigation information for your new pivot table experience, some direction for customizing your tables even further, shortcuts for updating your table with newer data, and reusing tables for different data sets (such as from other courses).

Showing the Pivot Table Fields panel again

Isn't it funny how sometimes when you're working with a program, you close something because you're done with it, but then can't figure out how to get it back? 

This one is easy.

First, click anywhere INSIDE the pivot table on the worksheet, then select Pivot Table Analyze from the main menu. That is where you will find most of the commands needed for working with pivot tables. But you must have your active cell IN the pivot table to make this command appear.

Next, click Show from the right side of the ribbon, the select Field List as shown below.

There! Your panel has returned! Now let's have some fun.

Add and Reorder and Reorganize Data Fields

One of the really fun things to do with Pivot tables is to drag the data fields around between the boxes to change WHAT data is presented and HOW it is presented. Remember, your data set, the original export, is NEVER changed throughout this process. Meaning you can make all the changes you want to your pivot table without worry.

YOU SHOULD, however, SAVE your file before you start doing this. That is, if you have a pivot table set up that you like. This way it will always be available to return to, in the event your moving things around goes horribly wrong.

ALSO you can create a NEW pivot table in the same file, keeping the original one intact. Just perform the steps for creating a pivot table again. You will get a new worksheet with the new table.

Add an additional metric value

One thing you might find useful, especially if you are using the Students Rows/Classes Columns layout exemplified above, is to have multiple metrics in the Value field.

For example, drag BOTH Video View % and Weighted Engagement into the Value field. For each student then, you will see two rows under their name, each with the labeled metric for that class (column). This is shown below.

Remember to change the Value settings for the newly added metric to Average and not Sum so that values like percentages show up properly, and so that both value fields are treated the same way.

Reorder the fields

If you have multiple fields in a particular area (row, column, value), try moving them around by dragging and dropping them into a different order inside the little box. The Pivot table view changes with your changes, so you can see immediately how it will affect the data presentation. 

In the above figure, there are two metrics in the Values field (video view % and weighted engagement % with weighted engagement listed first in the Values box. Clicking and dragging the weighted engagement metric down would move it to BELOW the video view % in the table.

As another example, the below figure (also shown earlier on this page) has both Lesson Date and Class in the Columns box.

The data set being used in this example has multiple classes with the 01-29 date, as well as multiple classes with the name "Some Preparation Videos". The ORDER of the fields in the Columns box determines how these things are grouped in the table. The above figure has all of the 01-29 classes together, with each named class as separate columns (and then a rollup/average of the metric).

If I change the order of those fields in the Columns box, the grouping changes to showing the Some Preparation Videos classes together, with each dated version of that class as separate columns. This is shown below.

Once again, we encourage you to move things around and add and remove selections from the boxes until you have a pivot table that you find useful.

Reusing Pivot Tables with Different Data

Once you have one or more pivot tables created that provide the data views you want, you may want to reuse those tables, rather than having to create new ones all the time.

The two use cases described here include:

• Reusing your pivot tables for multiple exports from the same course section across the term (e.g., early, mid-term, late in the term exports)
• Reusing the same pivot tables for ALL of your course sections exports

Your pivot table doesn't care where your data lives; it just needs to know where to get it. The steps below are designed to help you segregate different exports but use the same established pivot table(s). If that sort of organization doesn't matter to you, you can feel free to do it a different way!

Regardless of how you want to reuse the pivot tables, use the above instructions to create your pivot tables and configure them the way you want. THEN SAVE THIS FILE.

The easiest way to re-use established pivot tables is to have a file where they live and then open the XLSX file and Save As a new filename whenever you apply new exported data.

Adding New Data Worksheets to the XLSX with the Pivot Table

One method for reusing your pivot tables is to add worksheets to your XLSX file as you generate new data exports from EchoVideo. If you do this, it is recommended that you rename each of the data worksheets with the date of the export, so you can easily know which is which.

This method is a good way for consolidating all of the student data exports from a single section, then reviewing that data through the established pivot tables. Keep in mind, though, that your pivot table provides a view of ONE and ONLY ONE set of data/worksheet at a time. You can change which worksheet's data you are showing at any given time (using the second part of the below instructions), but you cannot view multiple data sets at one time.

Furthermore, the downloaded data file will continue to increase as more classes in your course occur (future classes do not appear in the downloads). This means your updated pivot table will have more columns (or rows if that was your configuration) to hold more classes' data.

To use a single pivot table for multiple worksheets (same file)

1. Download the new data CSV file from EchoVideo and open it with Excel.
2. Open the saved XLSX file containing the pivot table you want to use (it will open in a new Excel window).
3. At the bottom of the XLSX file, click the Plus button, shown below, to create a new empty worksheet tab.
   
4. Right-click on the new tab and select Rename from the menu that appears. This step is optional but will help you keep your data-tabs clearly delineated from one another.
   
5. Return to the NEW data download window and select all of the data in it (click in one of the cells and use Ctrl+A or Cmd+A, or click the top left corner of the sheet as shown in the procedures earlier on this page).
6. Press Ctrl+C or Cmd+C (for Windows or Mac respectively) to copy the selected data.
7. Return to the XLSX file with your pivot table and open the new worksheet you just created.
8. Select the field at Column A/Row 1 (top-left-most cell in the sheet) and press Ctrl+V or Cmd+V to paste the copied data.
9. If the data appears to have been pasted properly, SAVE the XLSX file. This is optional but ensures you have everything saved together before continuing.
10. Click on the tab containing the Pivot Table you want to use for the newly obtained dataset.
11. Click IN the pivot table to expose the Pivot Table Analyze option in the main menu.
12. From the Pivot Table Analyze ribbon, select Change Data Source then select Change Data Source from the sub-menu.
    
    

    The next thing that happens can be a little confusing, so stay with me.
    The Change Data Source dialog box appears and it resides ON TOP of the LAST USED data set worksheet. This sheet name and range is shown in the Table/Range field of the dialog box.

13. Click the NEW tab you just created from the bottom of the window, identified in the below figure. Don't worry, the Change Data Source dialog box will stay on top.
    
14. Click any field in the data set, then press Ctrl+A or Cmd +A to select all of the data.
    Don't worry, the Change Data Source dialog box will stay on top. 
15. Click OK in the dialog box.

Your pivot table returns with new data from the newly created and selected sheet.

You can repeat this as often as you want to. You can maintain ALL of the exports as separate worksheets in the file, or use the below instructions to maintain only ONE worksheet in the file. The Pivot Table doesn't care and simply uses the data you tell it to.

And remember, you can have multiple Pivot Table sheets in the single file, and point some or all of them to the new dataset. Each pivot table functions independently using the dataset you told it to use. You can also adjust how the data is being shown by changing the fields in the rows and columns as well as the values fields shown.

Reusing the Pivot Table on New Data in Same Worksheet

If maintaining a single file with multiple data set worksheets in it isn't for you, the alternative is to simply overwrite the original worksheet's data set with a new one. This might be a better option if you are already in the habit of keeping and filing your downloaded CSV files for your courses. 

The steps for this method are nearly identical to the above steps for using a new worksheet, but you paste the new data into the old sheet (removing the old data first) instead of into a new one.

To re-use a pivot table with a single worksheet (replacing the data)

1. Download the new data CSV file from EchoVideo and open it with Excel.
2. Open the saved XLSX file containing the pivot table you want to use (it will open in a new Excel window).
3. Return to the NEW data download window and select all of the data in it (click in one of the cells and use Ctrl+A or Cmd+A, or click the top left corner of the sheet as shown in the procedures earlier on this page).
4. Press Ctrl+C or Cmd+C (for Windows or Mac respectively) to copy the selected data.
5. Return to the XLSX file with your pivot table and open the tab for the existing data worksheet.
6. Select any of the cells in the worksheet and press Ctrl+A or Cmd+A to select all the data.
7. Press Delete. You should now have an empty worksheet.
8. Select only the field at Column A/Row 1 (top-left-most cell in the sheet) and press Ctrl+V or Cmd+V to paste the copied data.
9. Click on the tab containing the Pivot Table you want to use for the newly obtained dataset.
10. Click IN the pivot table to expose the Pivot Table Analyze option in the main menu.
11. From the Pivot Table Analyze ribbon, select Change Data Source then select Change Data Source from the sub-menu. 
    

    The Change Data Source dialog box appears and it resides ON TOP of the data set worksheet in the file. This sheet name and range is shown in the Table/Range field of the dialog box. 

    You MAY be able to simply Refresh the pivot table to get the new data to appear (see the Refresh button to the left of the Change Data Source button). HOWEVER, if your new data has more rows or columns than the original, you are better off re-selecting the entire data set as described below, so that you are sure to have everything.

12. Click any field in the updated data set, then press Ctrl+A or Cmd +A to select all of the data.
    Don't worry, the Change Data Source dialog box will stay on top.
    
13. Click OK in the dialog box.

Your pivot table returns with new data from the new data set pasted into the old sheet.

REMEMBER: After you have your new data in the pivot table, you can adjust how the data is being shown by changing the fields in the rows and columns as well as the values fields shown. Or save your updated file at this point to be sure your changes aren't lost.