Assignment 1

 

Assignment 1: How to create Pivot table in MS Excel

 

Definition of a Pivot Table:

A pivot table is a data summarization tool used in spreadsheet programs like Microsoft Excel or Google Sheets. It enables you to quickly analyze, summarize, and explore large datasets by organizing data in a compact and readable way. Pivot tables allow you to group, filter, and aggregate data, making them useful for tasks such as:

• Summarizing sales data

• Tracking website traffic

• Analyzing customer behavior

• Analyzing trends and patterns

In Excel, a pivot table dynamically updates based on the data you input, and you can easily modify it to present data in various ways.

---

Step-by-Step Guide to Creating a Pivot Table in Excel:

Step 1: Prepare Your Data

Make sure your data is structured properly in Excel, i.e., each column has a unique header (e.g., Date, Traffic Source, Sessions, Page Views).

Example of data you might export from Google Analytics:

Date	Traffic Source	Sessions	Page Views
2025-01-01	Organic Search	150	200
2025-01-01	Direct	120	150
2025-01-02	Organic Search	160	210
2025-01-02	Social Media	100	120

Step 2: Select Your Data Range

1. Highlight the Data:

   • Click and drag your cursor to select all the data you want to use for the pivot table (including the headers).

2. Ensure the Data is Structured Correctly:

   • Ensure there are no blank rows or columns in your dataset, as this can cause issues with the pivot table.

Step 3: Insert a Pivot Table

1. Open Excel:

   • Open the Excel file with your data.

2. Go to the “Insert” Tab:

   • At the top of Excel, click on the Insert tab.

3. Click on "Pivot Table":

   • In the Tables group, click the PivotTable button.

4. Select Data Range:

   • In the dialog box that appears, ensure that the data range is correct (Excel will usually auto-select the range based on your highlighted data). You can manually change the range if needed.

5. Choose Pivot Table Location:

   • You can choose to place the pivot table in a New Worksheet (which is recommended) or in an Existing Worksheet. If you choose the existing worksheet, click the desired cell location.

6. Click OK:

   • Once you’ve made your selection, click OK.

   • Step 4: Build the Pivot Table

     1. Pivot Table Fields Panel:

        • Once you click OK, you’ll be taken to the new worksheet with a blank pivot table and a PivotTable Fields panel on the right.

     2. Drag Fields into the Pivot Table Layout:

        • You'll see four main areas where you can drag and drop fields:
          • Rows: Drag a field here to group data (e.g., Date, Traffic Source).
          • Columns: Drag a field here if you want to break down the data by another attribute (e.g., Device Type, Traffic Source).
          • Values: Drag a field here to aggregate data (e.g., Sessions, Page Views). By default, Excel will sum the values.
          • Filters: Drag a field here to filter the data (e.g., by Date Range or Traffic Source).

     Step 5: Customize and Analyze Your Pivot Table

     1. Set Rows and Columns:

        • Drag Date to the Rows area to group data by date.
        • Drag Traffic Source to the Columns area to see traffic from different sources.

     2. Set Values:

        • Drag Sessions to the Values area. Excel will sum the sessions by default. You can also choose other aggregation options like Average or Count by clicking on the drop-down arrow next to the field in the Values area.

     3. Apply Filters (optional):

        • If you want to narrow down the data, drag a field (e.g., Date) to the Filters area and select a specific date range.

     Step 6: Format the Pivot Table

     1. Format Numbers:

        • Right-click on a value in the pivot table (e.g., Sessions), and choose Number Format to change how numbers are displayed (e.g., currency, percentage, etc.).

     2. Adjust Layout:

        • You can adjust the layout to make the table more readable. For example, you can change the report layout by going to the Design tab and choosing a different Report Layout option (e.g., Tabular form).

     3. Apply Styles:

        • In the Design tab, you can choose from various built-in pivot table styles to change the appearance of your table.

     Step 7: Analyze and Interpret the Data

     • Look at the summary results in your pivot table to analyze trends (e.g., which traffic source is bringing in the most sessions, or how traffic changes over time).
     • You can adjust the fields or the date range as needed to focus on different aspects of your data.

     Example Pivot Table Outcome:

     Traffic Source	2025-01-01	2025-01-02	Total
     Organic Search	150	160	310
     Direct	120	0	120
     Social Media	0	100	100
     Total	270	260	530

     This table shows the total sessions per traffic source, broken down by date.

     ---

     Step 8: Refresh the Pivot Table (if needed)

     If you update your data or import new data, you may need to refresh your pivot table:

     1. Right-click anywhere in the pivot table.
     2. Select Refresh to update the data in your table.