STATISTICAL SOFTWARE I STAT 114

Virgelio M. Alao
Associate Professor
Department of Statistics
Visayas State University
 Overview of Statistical Software

What is a statistical software (SS)?

- are specialized computer programs for statistical

analysis.
- these could be of different sources: open-source
SS; public domain SS; freeware SS; and
proprietary SS
 Overview of Statistical Software

What are the importance of using a statistical software?

• increasing demand of statistical data analysis because of

the big data and scientific research

• require a fast and efficient statistical software to perform

extensive data management and comprehensive data
analysis
 Overview of Statistical Software

What are the common statistical software used in data

management and data analysis?

➢Microsoft (MS) Excel, SPSS, SAS, Stata, and R which

have been utilized by people across all disciplines for
many years and are quite user friendly (Abatan and
Olayemi, 2014)
Data Management in MS Excel Module 1
Learning Outcomes Module 1

• Explain the importance of MS Excel in data management including the data

structure, use of formulas and functions.

• Understand and familiarize the techniques in data organization by creating

basic tables.

• Apply filtering and formatting techniques such as sorting, locating blanks,

removing blank rows, removing duplicates, combining and splitting cell
values, and flash fill in data management.

• Illustrate the use of pivot table tool in data management.

What is Microsoft (MS) Excel? Module 1

➢MS Excel is one of the most popular software applications

worldwide and is part of the Microsoft Office productivity
suite.
➢is a spreadsheet software that is often used to organize,
analyze and display data.
➢It is probably considered as the most common software for
data entry, for data storage, and for basic data analysis.
Why do we need to study (MS) Excel? Module 1

➢Extremely easy to use and interchanges nicely with other Microsoft products
➢Excel spreadsheets can be read by many other software like statistical
software
➢Add on module which is part of Excel for undertaking basic statistical
analyses
➢Can be used to perform statistical operations like data management, data
organization, data visualization, and data analysis
➢Widely used in data management and processing in private and government
agencies
➢Can produce very nice graphs for data visualization
Understanding Structured Data Module 1

➢While data in Excel can be laid out in many different ways some analytical
features require the data be in a specific structure.
➢For example:
creating tables, sorting, and /or filtering data will not work properly if
there are gaps in the data. Since Excel recognizes adjacent rows and columns
of data as a dataset, a blank row or column indicates the end of the data set,
which can give partial views of the complete data set.
Guidelines for Data Structure Module 1

➢Only one row of labels for the header row.

➢Each column contains only one type of data.
➢Continuous rows and columns of data; no gaps and no decorative rows or
columns.
➢Break data down into the smallest value necessary for sorting or filtering
e.g. An address should be broken down into columns
Address | Appt | City | State | Zip
➢Each row of data represents only one record.
e.g. A spreadsheet containing a list of individuals personal information, one
individual per row.
➢No duplicate rows of data.
MS Excel Basics Module 1

❖Definition of terms
❖Creating a Document
❖Cell basics
❖Formatting Cells
❖Number formats
❖Formulas and Operators
❖Relative and Absolute Cell References
❖Excel Functions
Functions Module 1

Different Functions work with different data types

• String
• Date
• Math
• Logical
• XLookUp
String Functions Module 1

• A "String" is some bit of text.

• It can contain numbers, but it isn’t meant to be added,
subtracted, compared, or other similar “math” functions.
• These functions are meant to change values in a string or
extract certain values from a string.
• Examples:
• Left, Mid, Right
• Concatenate
• Substitute
String Functions Module 1

Left, Right
• Return some number of characters from the left/right of a string.
• Examples and Syntax:
• =left( [string] , [number of characters] )
• =right( [string] , [number of characters] )
String Functions Module 1

Mid
• Return some number of characters from the middle of a string.
• Examples and Syntax:
• =mid( [string] , [starting character number] , [number of characters] )
String Functions Module 1

Concatenate (concat, &)

• Combine 2 or more strings of data
• Examples and Syntax
• =concatenate( [string1] , [string2] , [string3] ,….., [stringN] )
String Functions Module 1

Substitute
• Replace part of a string with a new value
• =substitute( [text] , [text to replace] , [new text] )
Date Functions Module 1

• Excel stores a date value as the number of days between the date
entered and December 31, 1899.
• This can be annoying, but it allows your computer to easily add and
subtract dates.
• Examples:
• Today, Now
• Month, Day, Year, Hour, Minute, Second
• DateDif
• NetWorkDays
Date Functions Module 1

Today, Now
• Returns the current date (today) or the current date and time (now)
• =today()
• =now()
Date Functions Module 1

Date Parts
(Month, Day, Year, Hour, Minute, Second)
• Return the given component of a date, time, or date and time.
Date Functions Module 1

DateDif
• Calculate the difference between two dates in the unit specified
• =datedif( [start date] , [end date] , [unit ( "Y" , "M" , or "D")] )
Date Functions Module 1

NetWorkDays
• Returns the number of work days between two dates.
• =networkdays( [start date] , [end date] , [Optional: Range of
Holidays] )
Math Functions Module 1

Math Symbols ( + - * / ^)
• Performs the calculation following the Order of Operations
• Nice to put parenthesis around items to ensure items are calculated in
order you expect.
Math Functions Module 1

Sum, Average, Max, Min

• Sum: total of all cells
• Average: average of all cells
• Max: greatest value of cells
• Min: smallest value of cells
Math Functions Module 1

Count / CountIf
• The "Count" function counts all data points in a given range.
• =count( [value1] , [value2] , [value3] ,…., [valueN] )
• The "CountIf" function only counts data points in a given range that
meet a specific criteria.
• =countif( [range] , [criteria] )
Logical Functions Module 1

• Logical Functions mostly deal with seeing if a given statement is

"True" or "False".
• These functions may do one thing if a statement is "True", and
something else if it is "False".
• Examples:
• If / Ifs
• And / Or
Logical Functions Module 1

If / Ifs
• If a condition is true, return a given value
• =if( [condition] , [value if true] , [value if false] )
• =ifs( [condition] , [value if true] , [condition] , [value if true], ……. )
Logical Functions Module 1

AND/OR
• AND reports "True" if all conditions are "True".
• =and([condition1],[condition2],….,[conditionN])
• OR reports "True" if at least one condition is "True".
• =or([condition1],[condition2],….,[conditionN])
XLookUp Functions Module 1

• Helps you lookup the value in one table and find a corresponding value in another
table.
• =XLookUp ( [value to lookup] , [column to look it up in] , [column to return if
match found] , [value to return if no match found] )
• Example:
• You have a data set with a course prefix (“ENGL”) in one column and want the
full academic department name (“English”).
• Somewhere else in your file, you have a table with course prefixes (“ENGL”)
and their corresponding department names (“English”).
XLookUp Functions Module 1

• Works best when your “lookup” table has only 1 row per identifier
XLookUp or VLookUp Functions Module 1

• VLookUp requires the value you are matching to be the first column of the
“look up” table
• VLookUp can only return one column, where XLookUp can return many
• XLookUp defaults to an exact match search
• XLookUp has a built in “Not Found” parameter
Rules for Data Management using MS Excel [1]: Module 1

1. Only the data must be entered in a worksheet. Have a

separate worksheet in the same spreadsheet when you do the
analysis. Never ever copy/cut and paste the data for use
elsewhere in the spreadsheet/workbook/worksheet to avoid
confusion. Always refer to the cell(s) containing the original
data.

[1] [Link]
Rules for Data Management using MS Excel [1]: Module 1

2. Try to use shorter variable names (i.e. column headings),

say, not longer than 8 characters but should be understandable.
Furthermore, do not use variable names that contain spaces,
start with a number, or include slashes or commas. If you need
spaces to make the title clear (e.g. “Date of birth”) either use
underscores (“Date_of_birth”), or capitalization to indicate new
words (e.g. “DateOfBirth”) or clear abbreviations (e.g. “dob” or
“DOB”).

[1] [Link]
Rules for Data Management using MS Excel [1]: Module 1

3. Always use the same spelling/format for entering data. For

example, don’t use “1990 DEC 22” and “22.12.90”,
“22.12.1990”, “22/12/90” and “22/12/1990”. Similarly, don’t
use “Female”, ‘female”, “F” and “f”. In the case of categorical
variables, it is often best to use numbers rather than text as it
will make reformatting the data easier later on. It’s also best to
start with “0” rather than “1”, as this makes regression type
analyses easier to interpret. For example, you might use “0” for
placebo, “1” for treatment 1 and “2” for treatment 2. Just
choose one format and stick to it.
[1] [Link]
Rules for Data Management using MS Excel [1]: Module 1

4. Do not use color coding to classify data because other

software that you might use to analyze your data later on
cannot access this color coding. Just use a new column for the
classification with a coded number.

[1] [Link]
Rules for Data Management using MS Excel [1]: Module 1

5. Do not use comments under any circumstances because

other software that you might use to analyze your data later on
cannot access these comments. If there is a need to include
“Notes” for a given subject (row), then use a “Notes” column
with an entry in the appropriate row (also refer to point 7).
Others just remove this “notes” column during import into the
other software.

[1] [Link]
Rules for Data Management using MS Excel [1]: Module 1

6. Do not round off values if they are calculated especially if

the digits are significant because you will only compound the
rounding errors if you use this in a subsequent calculation.

[1] [Link]
Rules for Data Management using MS Excel [1]: Module 1

7. When blank cells are acceptable in your data (e.g. a

missing sample), consider some other descriptive value, such as
“na” “NA”, “.”or even a number that is incompatible with the
nature of the missing data (e.g. “-1” in a weight field). Do not
use “0” as this is a number and not a missing value because
there are other software that can recognize empty cells
appropriately.

[1] [Link]
Rules for Data Management using MS Excel [1]: Module 1

8. Use one data type per column (e.g. dates/numbers/text).

9. Use one spreadsheet per data set. Similarly, do not leave

entire empty rows or columns.

10. Do not merge cells in your spreadsheet because other

software cannot access datasets with merged cells.

[1] [Link]
Rules for Data Management using MS Excel [1]: Module 1

For example,

[1] [Link]
Ways of Entering Data in Excel Module 1

❑ Manual Entry (The Basics)

➢ Useful for small data sets through direct typing and applying Fill Handle

❑ Using Data Entry Forms (Excel Forms Tool)

➢ Useful in large data sets say from survey data which prevents you from accidentally
overwriting other rows

❑ Data Validation (Drop-down Lists)

➢ ensures data entry remain “clean” which limits what can be entered

❑ Importing External Data (Power Query)

➢ you often get data from external sources like government PDFs, CSV files, or web
databases
Entering Data More Efficiently Module 1
Using the Fill Handle Module 1
Using the Fill Handle Module 1
Using the Excel Form Tool Module 1

You can add the "Form"

button to your Quick
Access Toolbar, i.e. File
→ Options → Quick
Access Toolbar. When
you select your table
headers and click
"Form," it creates a
clean dialogue box for
entering one record at
a time. This prevents
you from accidentally
overwriting other rows.
Using the Excel Form Tool Module 1

Alt + D + O (Press Keys One at a Time)

Headers
1) Make sure your table has headers
2) Click anywhere in the given table Create New Data
3) Press Alt → D → O, one at a time
4) Select “New”
5) Enter in data
Using the Drop-Down List Module 1
Using the Drop-Down List Module 1

1) Click on the Data

Tab
2) Click Data
Validation → Data
Validation
3) Allow: Select Lists
4) Source: Implement
your given criteria
(select cell range)
5) Select ok
Source: Is the
information that
you want to be
in your drop-
down list

Showing
drop-down
menu
Importing Data From Different Sources/Format Module 1

On the Data tab

select Get Data
then select the
source of your data
to be imported in
Excel, i.e. from
file, database, or
other sources
Cleaning Up Raw Data: Formatting Techniques Module 1

➢Before you are able to begin working with data, it may be necessary to
ensure there are no problems within the data.
➢Excel offers several tools to speed this process up significantly; duplicate
removal, blank spaces removal, splitting combined elements into component
data, and combining data into new columns of required information.
➢It is a good idea to quickly check for and correct possible issues early on to
avoid issues further down the road.
Formatting Techniques: Removing Unwanted Spaces Module 1
Formatting Techniques: Removing Unwanted Spaces Module 1
Formatting Techniques: Eliminating Duplicates Module 1
Formatting Techniques: Eliminating Duplicates Module 1
Formatting Techniques: Conditional Formatting Module 1

- This is a very useful tool that allows you to identify potential errors in
your data.
- Highlight cells based on their value.
- Useful to callout cells that are above or below a given criteria.
• "Show me all people with more than 50 volunteer hours.“
- Can also give a range of colors showing how records rank on a given
column.
• "Let me see who has done the most / fewest volunteer hours easily."
Formatting Techniques: Conditional Formatting Module 1
Formatting Techniques: Eliminating Duplicates Module 1
Conditional Formatting: Locating Blanks Module 1

▪On the Home tab

click Conditional
formatting then New
Rule → Format only
cells that contain →
cell value select
blanks → Format
then select the color
to easily identify the
blanks → Ok
Conditional Formatting: Removing Blank Rows or Columns Module 1

▪From the highlighted rows or

columns with blanks, you can now
remove by simply going to Home
tab → Delete → select Delete
Sheet Rows/Columns
▪Note that if only a specific cell is
blank you will need to double
check your data if it is an
empty/missing value
Formatting Techniques: Flash Fill Module 1

Automatically fill in values/data

From Data Tab then click Flash Fill
Formatting Techniques: Moving Data/Text from a Row or Column Module 1

1) Select the data that you want to move

2) Click on the green border, then drag to
new location
Formatting Techniques: Text to Columns Module 1

1) Highlight text that will be

converted into columns
2) Paste into Excel document
→ Highlight cell range
A1:A5
3) Click the Data tab → Text
to Column
4) Select the criteria, which
best fits the data (ex: the
data has commons,
therefore “delimited” is
the best option → Next
5) Select “Comma” in the
delimiters selection →
Next → Finish
Data Organization Using Table Module 1
Creating a Table Module 1

On the Home tab select Format as Table

then click one of the styles to select the
range of data
Or On the Insert tab select Table i.e. with
default style
Creating a Table Module 1

You can apply filtering on the data set depending

on what you want. You can click the autofilter
button and check the data that you want to be
displayed or uncheck otherwise
Pivot Table Module 1

▪ Useful in arranging and summarizing large

raw data
▪ In creating pivot table, on the Insert tab
select PivotTable. Choose From Table/Range
→ Enter the cell range of your data in Excel
→ select new worksheet
Pivot Table Module 1
Protecting Your Data in Worksheet Module 1

▪Your data and your works in Excel

can be protected
▪On the Review tab select Protect
Worksheet → select what you want
to protect → OK
➢ To prevent other users from
accidentally or deliberately
changing, moving, or deleting
data in a worksheet, you can
lock the cells on your Excel
worksheet and then protect the
sheet with a password.
Protecting Your Data in Workbook Module 1

▪On the Review tab select Protect

Workbook → select what you want
to protect → OK
➢ To prevent other users from
viewing hidden worksheets,
adding, moving, deleting, or
hiding worksheets, and
renaming worksheets, you can
protect the structure of your
Excel workbook with a
password.
➢ To lock your file so that other
users can't open it
Execution in Excel Module 1

▪Using the file “Example Formatting_Pivot.xls”

End Module 1

Thank you
for your participation!