INTRODUCTION TO

Microsoft EXCEL
COMPUTER APPLICATIONS IN MODERN INFORMATION SOCIETIES
DEPARTMENT OF SOCIO-COMPUTING
 Module 2: Data Visualization
CONDITIONAL FORMATTING
Conditional Formatting
✔Conditional formatting makes it easy to highlight certain values or
make particular cells easy to identify.
✔This changes the appearance of a cell range based on a condition (or
criteria).
✔You can use conditional formatting to highlight cells that contain
values which meet a certain condition.
✔Or you can format a whole cell range and vary the exact format as the value
of each cell varies.
✔It offers a way of visualizing your data through colors.
Conditional Formatting
(CONT’D)
✔Let’s say that I want to know who got A- or above in their final marks.
✔I have a certain criterion (which is getting at least an A-; this means
that we want those who got 90 or above)
✔to do this, I will use conditional formatting to color the ones with 90 or
above and visualize their data
Conditional Formatting
(CONT’D)
Start by highlighting the cells you
want (Select your data)

Choose Conditional Formatting

Command from the Home Tab
Conditional Formatting
(CONT’D)
✔You will get many pre-
sets, “rules”, to apply
your conditional
formatting.
✔For our example: we
want the Highlight ones.
Conditional Formatting
(CONT’D)
✔Choose “Greater than” as 1
we are looking for marks
higher than 89 (90 or above) 2 3
 Conditional Formatting
(CONT’D)

write your
condition

Choose the highlight color you

want
Conditional Formatting
(CONT’D)
✔There are other presets that we could use for conditional formatting.
✔To know more about each of them, just mouseover and read about it
from the menu.
Conditional Formatting: Data
Bars
✔They are like bar graphs;
the higher the value the
longer the bar in the cell.
Conditional Formatting: Color
Scales
✔Changes the background
color based on the value of
the cell.
Conditional Formatting: Icon
Sets
✔They can be used to add
different symbols, ratings and
indicators.
✔It helps us see which items
are the highest and which are
the lowest compared to one
another.
Remember Filtering?
✔What if we had a big list of students, and we have used the conditional
formatting to highlight which ones passed and which ones did not pass,
but we still wanted a way to see only those who passed/did not pass?
✔You already learned earlier how to filter data by using text filters, so
you know how to do this by text.
✔But we can also use the formatting to filter!
Filtering by Color
✔We used conditional
formatting to color
the cells of those who
passed green and 1 2
those who did not
pass red
Filtering by Color
✔You know that green
means passed, and red 3
means did not pass, so go
ahead and choose the
color/data you are interested 4
in seeing.
Filtering by Color

5
Filtering by Color
 Module 2: Data Visualization
CHARTS
Creating Charts
✔Returning to exercise (3):
✔Create a column chart to represent the following table
Creating Charts (CONT’D)
1

Choose the table. Click on Insert tab,

then Column and choose the type of
column chart you want
Creating Charts (CONT’D)

Note the Chart Design and

Format Tabs
Switch Data

To switch the data

(make years on x-
axis and budget
figures on y-axis)
click on Switch
Row/Column
 Apply a Chart Layout

From Design tab,

From Design tab, choose a Style
choose a Quick
Layout
 Change Style of a Chart

Change the chart

Colors from the
Design tab
Move Chart
1
2

To Move a chart into a

separate worksheet, click
on Move Chart

Choose New Sheet and

4
Type in the name of the
chart
Move Chart (CONT’D)

Chart placed in a new sheet with the

name you’ve specified
Change Chart Type
✔Change the chart type to be a pie chart for Income
1

To change chart type, click on

Change Chart Type
Copying Charts to Other
Documents
✔To copy the chart into another
document (word document)
✔Right Click on the chart > select
Copy
 Copying Charts to Other
Documents

Open new word

document and click
Paste
Exercise (4)
✔Create a scatter diagram for the following supply and demand figures
Exercise (4) (CONT’D)
2

Choose the table. Click on Insert tab,

then Scatter and choose the type you
want
Exercise (4) (CONT’D)

Click on Chart Title and type in Title

“Demand and Supply”
 Module 2: Data Visualization
PIVOTTABLES AND PIVOTCHARTS
PIVOTTABLES
PivotTables and
PivotCharts
✔You can use a PivotTable to summarize,
analyze, explore, and present summary
data.
✔PivotCharts complement PivotTables by
adding visualizations to the summary
data in a PivotTable, and allow you to
easily see comparisons, patterns, and
trends.
✔PivotTables work a little bit differently
depending on what platform you are
using to run Excel.
Pivot Tables (CONT’D)
✔ In the presented sheet, we
have some sales related
data.
✔ Let’s visualize it and find
the patterns we are
interested in.
✔ For example, let’s find out
how many of a certain
product each segment has
bought.
Pivot Tables (CONT’D)

Start by highlighting the data you want for your pivot

table.
Pivot Tables (CONT’D)
✔ Choose Insert Tab.
✔ Click on Pivot Table. 1
2

NOTE: if you mouseover it, you can read more about what
it does. You can also click on Tell me more to help you.
Pivot Tables (CONT’D)
✔ After you click on Pivot Table, you
get to choose the range of data
(which we did when we selected
them beforehand) and choose
where you want your table to be (a
new worksheet or an existing one)
Pivot Tables (CONT’D)
Pivot Tables (CONT’D)
To add your rows
✔ A new sheet and columns Sometimes you will need to
will be created, filter your data to select
and the screen certain criteria like years,
should be like nationalities and so on. This is
this. Let’s why you can add what filter
understand you want in here.
what we see
here:

You get to choose which values

are counted in the Value Field.
Pivot Tables (CONT’D)
✔ This is where you will drag your filters,
rows, values and columns to add them
below.
✔ Doing this will make them appear in the
sheet automatically.
Pivot Tables (CONT’D)
✔ Now let’s find out how many of a
certain product each segment has
bought.
✔ We do not need any filters for now.
✔ We want the segment and the
product columns from our original
excel sheet.

✔ When I chose them, this happened.

✔ Now, let’s make it prettier :))
Pivot Tables (CONT’D)
✔ Let’s say we want to see product
names and the segments that
bought them (we add them as rows
and columns)
Pivot Tables (CONT’D)
✔ Now, we want the values (the
number) of those products to
know how many of each
product each segment buys.
Pivot Tables (CONT’D)
✔ Please note how Excel changed
the word “Product” into “Count of
Product” when dragged to the
Values.
✔ It also counted it in the Values
Field in the Pivot Tables itself.
Pivot Tables (CONT’D)
✔ Let’s say we want to filter by year:
1. Choose the data you want to filter by.
2. Drag it down into the Filters box.
3. It should look like this:
Pivot Tables (CONT’D)
✔ It should be added to the table like this:
Pivot Tables (CONT’D)
✔ Click on “Select Multiple Items” to
allow you to choose the year you want.
✔ Let’s say I want to see year 2013 sales:
Pivot Tables (CONT’D)
✔ You can also filter the
products\segments you want
to see or sort them
Pivot Tables (CONT’D)
✔ You can also filter the
products\segments you want to
see or sort them
 Module 2: Data Visualization
PIVOTTABLES AND PIVOTCHARTS
PIVOTCHARTS
PivotCharts

Start by highlighting the data you want for your pivot

chart
PivotChart (CONT’D)
✔ Choose Insert Tab.
✔ Click on PivotChart. 1

2
PivotChart (CONT’D)
✔ After you click on PivotChart, you
get to choose the range of data
(which we did when we selected
them beforehand)
✔ Choose where you want your chart
to be (a new worksheet or an
existing one)
PivotChart (CONT’D)

Select Product and Profit as Example

PivotChart (CONT’D)

Select Specific Product(s) and Sales Per Month as an Example

Note: You can change Chart Type as well.
 Module 2: Data Visualization
DYNAMIC DASHBOARD
An interactive Excel dashboard is a data management tool that harnesses the
power of Excel data analysis tools such as Pivot Tables and Pivot Charts to
track, analyze, monitor, and display key business metrics.

In an interactive Excel dashboard, data becomes visually meaningful, and with

/
tools such as slicers, users can interact with the data enabling them to derive
important insights and make data-driven well-informed decisions.

Dynamic Dashboard
 ✔ Do you want to create a dashboard that will update all of your charts
Dynamic Dashboard simultaneously based on what filters your users select?
✔ Follow the following steps and you can create a professional-looking
dashboard without having to use any complex formulas or
programming.
(CONT’D)
Dynamic Dashboard
(CONT’D)
Step 1: Preparing the Data
✔ If you have data in Excel that you want to use to create a
dashboard, there are a couple of things you’ll want to do first
to make sure everything goes well.
1. Ensure your data is free of error cells, as this will result in errors.
2. Have proper headings setup so that you know what you are referencing
in your dashboard.
3. Setup a named range for your data, ideally a dynamic one. This will make
it easier to link everything to your data quickly and easily.
 Dynamic Dashboard
(CONT’D)
Step 2: Setting up the Pivot Table(s) and
PivotChart(s)
Select Data and Choose the
“Pivot.tables” Worksheet, and
Press Ok
1
Create A
“Pivot.tables” Sheet 2

Select Dates and 4

Total Sales, for the
First Table
Dynamic Dashboard
(CONT’D)

Select Products and Select Stores and

Total Sales, for the Total Sales, for the 3rd
2nd Table Table
Dynamic Dashboard
(CONT’D)

Select Stores,
Salesperson and Total
Sales, for the 4th Table
Dynamic Dashboard
(CONT’D)

Create the Pivot Chart 6

for each Table
Dynamic Dashboard
(CONT’D)
✔ The number of options you have on here depends on how many
fields you added to your pivot table. Whether you make the
changes on your pivot chart or pivot table doesn’t matter, the
chart will update all the same.
Dynamic Dashboard
(CONT’D)
✔ However, for the
purposes of a dashboard,
we are going to get rid of
these ugly filter buttons
on the chart.
✔ To do this, click on your
chart and click the button
for the Field Buttons and
this will remove the
buttons.
Dynamic Dashboard
(CONT’D)

Do the other figures and Copy them

into “Dynamic Dashboard” Sheet
Dynamic Dashboard
(CONT’D)
8
9
Add Slicers from PivotChart Analyze,
Select (Store, Salesperson, Product,
and Months), and Press Ok

10

11
Dynamic Dashboard
(CONT’D)
Once you’ve inserted slicers, you want to make sure that
each slicer is connected to every pivot table.
To do this, simply right-click on the slicer and
11 select Report Connections.
Repeat this step for every slicer you create.

12
Dynamic Dashboard
(CONT’D)
13

Redesign and rearrange the

Dashboard
Dynamic Dashboard
(CONT’D)
Thank You