Advanced Data Visualization
Techniques in Excel
By: Pearly Ann A. Escalambre
Objectives
• Creating Interactive Dashboards
• PivotTable Slicers and Filters
• Visualizing Data With PivotCharts
Advanced Data Visualization
Techniques in Excel
• 1. Conditional Formatting - Conditional formatting allows users to
apply specific formatting rules to cells based on their values. This
technique is effective for highlighting patterns and trends within
datasets. (use color scales to visually represent ranges of values).
• 2. Interactive Dashboards with Pivot Tables - Pivot tables enable the
summarization and analysis of large datasets, and when combined
with slicers and filters, they can create interactive dashboards.
• 3. Advanced Charting Options - Excel provides various advanced chart
types like combo charts (line and bar), scatter plots, and bubble
charts.
Pivot Table
• It allows users to summarize, analyze, explore, and present large
datasets in a concise and interactive format.
• Here are some Steps to activate your Pivot Table in Excel
• Prepare Your Data
• Select Your Data
• Insert the Pivot Table
• Choose the Data Range
• Click OK
• Add Fields to Your Pivot Table
• Adjust Calculations (if needed)
• Sort and Filter Your Data
• Format Your Pivot Table
STEP-BY-STEP
• Prepare Your Data: Ensure your data is organized in a table format with clear
headers for each column. Avoid blank rows and columns.
• Select Your Data: Click on any single cell within the dataset you want to analyze.
• Insert the Pivot Table: Navigate to the Insert tab on the Ribbon.
• In the Tables group, click on PivotTable. This will open the Create
PivotTable dialog box
• Choose the Data Range: Excel will automatically select the data range for you, but
you can adjust it if necessary.
• Decide where you want to place the Pivot Table: New Worksheet (recommended
for clarity) or
• Existing Worksheet (specify the location)
STEP-BY-STEP
• Click OK: After confirming your selections, click OK. This will create a
blank Pivot Table and open the PivotTable Fields pane
•.
• Add Fields to Your Pivot Table: Drag and drop fields from the
PivotTable Fields pane into one of four areas:Rows: To categorize data
vertically.
• Columns: To categorize data horizontally.
• Values: To perform calculations (like sum or count).
• Filters: To filter data based on specific criteria
STEP-BY-STEP
• Adjust Calculations (if needed):By default, data will be summarized by sum or
count. To change this, right-click on any value in the Values area, select Value
Field Settings, and choose your desired calculation method (e.g., average,
count)
•.
• Sort and Filter Your Data: You can sort your data by clicking on any cell within
the column you wish to sort, right-clicking, and selecting sort options.
• Use filters by clicking on the dropdown arrows next to Row Labels or Column
Labels to narrow down your data view
•.
• Format Your Pivot Table: Use the options under the Design tab that appears
when you click on your Pivot Table to change its appearance and layout as
needed.
• Update Your Pivot Table: If your source data changes, right-click on your Pivot
Creating Interactive Dashboards
Creating Interactive Dashboards
PivotTable Slicers and Filters
Advanced Charting
Hands On Activity
Direction: Encode
the following Data
Sets in Any Excel
Platform and Insert
Pivot Table, Make
sure that your
Pivot Table is nicely
Formatted and
Updated. (50 pts)
Hands On Activity
Direction: With the
same Data Sets,
Make an
Interactive
Dashboards and
also Try yourself an
Advance Charting
Techniques.
(50 pts EACH)
Midterm Exam Pointers to
Review
• Data Collection and Preparation • Data Analysis ToolPak
• Basic Data Quality Assessment • Hypothesis tests and t-tests
• Importing Data from Various • Data Exploration and Visualization
Sources • Charts and Graphs
• Data Cleaning and Preprocessing in • Bar Charts, Line Charts, Heatmaps
Excel
• Histogram, Box Plots, Scatter Plots
• Handling Missing Data and Outliers
• Advanced Data Visualization
• Descriptive Analytics Techniques in Excel
• Mean, Median & Standard • PivotTable
Deviation
• PivotCharts
