By: Peter K. Mulwa MSc (UoN), PGDE (KU), BSc (KU)

(1)

SPREADSHEETS FOR MARKETING &

SALES TRACKING - DATA ANALYSIS

TOOLS USING MS EXCEL

By: Peter K. Mulwa

(2)

Objectives

By the end of the session, participants should be

able to:

(3)

Ms Excel for Data Analysis

• Ms Excel provides powerful tools that for data

analysis which include:

a) Sort: Help to arrange data in either ascending or

descending order. You can sort your data on one column or multiple columns

b) Filter: This is a tool used to display records that

meet a certain criteria

c) Conditional Formatting: Conditional formatting

(4)

Ms Excel for Data Analysis

d) Charts: Enables one to present data in graphical

form

e) Pivot Tables: A pivot table allows one to extract the

significance from a large, detailed data set. It enables one to view data in summarized form which enable one to draws a meaning from the data

f) Tables: Tables allow one to analyze data quickly and

(5)

Ms Excel for Data Analysis

g) What-If Analysis: What-If Analysis in allows one to

try out different values (scenarios) for formulas and predict the possible outcomes based on different scenarios

h) Solver: This tool uses techniques from the

operations research to find optimal solutions for all kind of decision problems

i) Analysis ToolPak: The Analysis ToolPak is an Excel

(6)

Sort

• Steps:

i. With an open worksheet, click inside the data that you want to sort

ii. Click on data tab

(7)

Sort

(8)

Filter

• One can do simple filter or advanced filter • Steps for simple filter:

i. Click inside the data you want to filter ii. Click on the data table

iii. Click on filter under sort & filter group

iv. Click on the drop arrow next to the field you want to filter by

v. Select the field to filter by vi. Click Ok

NB: Click on filter under sort & filter group again to clear

(9)

(10)

Filter

Steps for Advanced filter:

i. Set the criteria range in two cells on adjacent rows using a field name and the condition

ii. Click inside the worksheet iii. Click on data tab

(11)

Filter

v. Select the list range and the criteria range

vi. You can copy the filtered data in another location within the same sheet or a different sheet by choosing the copy to another location option on the dialog box

(12)

PivotTables

Steps:

i. Click on insert tab

ii. Click on pivot tables from the ribbon

(13)

PivotTables

(14)

PivotTables

• In our sample data, to get the total amount exported for each product, drag the following fields to the different areas;

1. Product Field to the Row Labels area 2. Amount Field to the Values area

3. Country Field to the Report Filter area

(15)

(16)

PivotTables

(17)

PivotChart

• Steps for inserting PivotChart i. Click on the PivotTable

ii. Click on PivotTable tools

iii. Under options, click on PivotChart

iv. From the insert chart dialog box, select the type of the chart to use e.g. 3-D Clustered Column

v. Click Ok

NB 1: Under PivotChart tools, you can change chart layout

options as you want e.g. chart title, chart axis titles, etc

NB 2: The chart display changes as you change the filter

(18)

(19)

(20)

WHAT-IF ANALYSIS

• What-If Analysis in allows one to try out

different values (scenarios) for formulas and

predict the possible outcomes based on

different scenarios

• Steps

i. Click on your worksheet ii. Click on data tab

iii. Click on What-if Analysis on the data ribbon

(21)

Using the Scenario Manager

• Click on Scenario Manager from

What-if-Analysis drop down list

(22)

v. Click on add to add a Scenario vi. Type scenario name

vii. Choose the changing cells e.g. Quantity viii. Enter new values for the changing cells ix. Click ok

(23)

• Using the Goal Seek

i. Select the cell containing the formula that will return the result you’re seeking; in this example, cell G5

ii. On the Data tab, choose What-If Analysis→Goal Seek in the Data Tools group

(24)

iii. Select the To Value text box and enter the goal

iv. Select the By Changing Cell text box and select the cell that you want to change

v. Click OK

(25)

(26)