Data Analysis

Quest
Introduction to Data
analysis
Data is basically a collection of facts
or information, and through
analysis, you'll learn how to use the
data to draw conclusions, and
make predictions, and decisions.
The Six Data ●
●
Ask
Prepare
Analysis Phases ● Process
● Analyze
● Share
● Act
Phase 1 - Ask

It’s impossible to solve a problem if you don’t know what it is. These are some things to consider:

● Define the problem you’re trying to solve

● Make sure you fully understand the stakeholders expectations

● Focus on the actual problem and avoid any distractions

● Collaborate with stakeholders and keep an open line of communication

● Take a step back and see the whole situation in context

Questions to ask yourself in this step:

➔ What are my stakeholders saying their problems are?

➔ Now that I’ve identified the issues, how can I help the stakeholders resolve their questions?
Phase 2 - Prepare

You will decide what data you need to collect in order to answer your questions and how to organize it so that it is
useful. You might use your business task to decide:

● What metrics to measure

● Locate data in your database

● Create security measures to protect that data

Questions to ask yourself in this step:

➔ What do I need to figure out how to solve this problem?

➔ What research do I need to do?

Phase 3 - Process

Clean data is the best data and you will need to clean up your data to get rid of any possible errors, inaccuracies, or
inconsistencies. This might mean:

● Using spreadsheet functions to find incorrectly entered data

● Using SQL functions to check for extra spaces

● Removing repeated entries

● Checking as much as possible for bias in the data

Questions to ask yourself in this step:

➔ What data errors or inaccuracies might get in my way of getting the best possible answer to the problem I am trying
to solve?

➔ How can I clean my data so the information I have is more consistent?

Phase 4 - Analyze

You will want to think analytically about your data. At this stage, you might sort and format your data to make it
easier to:

● Perform calculations

● Combine data from multiple sources

● Create tables with your results

Questions to ask yourself in this step:

➔ What story is my data telling me?

➔ How will my data help me solve this problem?

➔ Who needs my company’s product or service? What type of person is most likely to use it?
Phase 5 - Share

Everyone shares their results differently so be sure to summarize your results with clear and enticing visuals of your analysis
using data via tools like graphs or dashboards. This is your chance to show the stakeholders you have solved their problem
and how you got there. Sharing will certainly help your team:

● Make better decisions

● Make more informed decisions

● Lead to stronger outcomes

● Successfully communicate your findings

Questions to ask yourself in this step:

➔ How can I make what I present to the stakeholders engaging and easy to understand?

➔ What would help me understand this if I were the listener?

Phase 6 - Act

Now it’s time to act on your data. You will take everything you have learned from the previous phases and put it to
use. This could mean providing your stakeholders with recommendations based on your findings so they can make
data-driven decisions.

Questions to ask yourself in this step:

➔ How can I use the feedback I received during the share phase (step 5) to actually meet the stakeholders
needs and expectations?
Conclusion

These six steps can help you to break the data analysis process into smaller, manageable parts, which is
called structured thinking. This process involves four basic activities:

1. Recognizing the current problem or situation

2. Organizing available information

3. Revealing gaps and opportunities

4. Identifying your options

It is normal to feel pulled in a few different directions as a data analyst due to the role and expectations.
Following processes like the ones outlined above and using structured thinking skills can help get one back on
track, fill in any gaps and let you know exactly what you need.
 Key data analyst tools

Spreadsheets

Data analysts rely on spreadsheets to collect and organize data. Two popular spreadsheet applications you will probably use a lot in
your future role as a data analyst are Microsoft Excel and Google Sheets.

Databases and query languages

A database is a collection of structured data stored in a computer system. Some popular Structured Query Language (SQL) programs
include MySQL, Microsoft SQL Server, and BigQuery.

Visualization tools

Data analysts use a number of visualization tools, like graphs, maps, tables, charts, and more. Two popular visualization tools are
PowerBI, Quicksight, Tableau and Looker.
 Choosing the right tool

Spreadsheets Databases

Software applications Data stores - accessed using a query language

(e.g. SQL)

Structure data in a row and column format Structure data using rules and relationships

Organize information in cells Organize information in complex collections

Provide access to a limited amount of data Provide access to huge amounts of data

Manual data entry Strict and consistent data entry

Generally one user at a time Multiple users

Controlled by the user Controlled by a database management system

 Resources

Spreadsheets

Google Sheets Training and Help

Google Sheets Cheat Sheet

Microsoft Excel for Windows Training

SQL

W3Schools SQL Tutorial

SQL Cheat Sheet

Data Analysis
Quest

Day - 2
 Learn more about spreadsheet basics

Below, you will find a list that covers two types of spreadsheet programs: Microsoft Excel and Google Sheets. The list
includes quick-start guides, tutorials, and more. The examples in this course use Google Sheets, but you can follow along
using Excel or any other spreadsheet application. The user interface might be a little different, but it should look and work
similarly.
 Microsoft Excel

Microsoft Excel
● Office Quick Starts: Scroll down to the Downloadable guides section to download the Excel Quick Start Guide: This PDF guide begins with a
labeled map of Excel that can guide you through the basic tasks you can accomplish in Excel. For tips on starting and opening Excel, this
Microsoft Support page will show you how to begin a new workbook.
● Excel video training: This is a collection of step-by-step videos to use all sorts of Excel features, including adding and working within rows,
columns, and cells; formatting; using formulas and functions; and adding charts and pivot tables.
● Sort data in a range or table: This page guides you through all of the steps you will need to sort data by number, text, and color. You’ll also have
the option to sort by custom list so that you can customize exactly what you want to sort.
● Filter data in a range or table: This article has step-by-step instructions on how to filter an Excel spreadsheet to show only the data you want to
see. You can also use built-in comparison operators, such as “greater than” and “top 10” to reveal only the most relevant data.
● Format a worksheet: The guide will help you select and format your Excel spreadsheet, then change the borders, shading, colors, and text. This
can help improve your spreadsheet’s readability.

Pro tip: If you’re searching for information about using customizable options, check out Microsoft’s Guidelines for organizing and formatting data on a worksheet.
This article provides clear methods for creating easy-to-read spreadsheets.
 Google Sheets

Google Sheets
● Google Sheets cheat sheet: The cheat sheet puts all the basics of Sheets on a single page for easy reference. Here, you can learn about
customizing your spreadsheet and the data inside; working with rows, columns, and cells; sharing your spreadsheet with others; creating different
versions and copies of a spreadsheet; and more.
● Get started with Sheets: Create and import files: This guide is a step-by-step guide for working with Sheets. You start by learning how to open a
spreadsheet, then move on to adding data.
● Sort and filter your data: This resource can help you organize data in Sheets. Use this guide to sort part or all of a spreadsheet. You can sort by
text, number, and color. Then, learn how to create filters to show only certain data while hiding the rest. Finally, the article includes information on
creating, saving, and removing a filter view.
● Edit and format a spreadsheet: This will help you make easy-to-read spreadsheets. You will learn how to assign a color, customize borders
around cells, and change the appearance of text. If you’d like to give your spreadsheet a theme, you can scroll to the bottom of the page and find
how to apply it to parts of your spreadsheet.

Tip: Microsoft Excel and Google Sheets are very similar in terms of calculations, formulas, functions, and many other features. But there are some differences,
which can make it tricky to switch from one to the other. If you are moving between Excel and Google Sheets, find a quick list of the differences between the two
kinds of spreadsheet applications in Overview: Differences between Sheets and Excel.
 Formulas

A formula is a set of instructions that does a specific calculation using the data in a spreadsheet. Formulas make it easy for
data analysts to do powerful calculations automatically, which helps them analyze data more effectively. Below is a
quick-reference guide to help you get the most out of formulas.

The basics
● When you write a formula in math, it generally ends with an equal sign (2 + 3 = ?). But with formulas, they always
start with one instead (=A2+A3). The equal sign tells the spreadsheet that what follows is part of a formula, not
just a word or number in a cell.
● After you type the equal sign, most spreadsheet applications will display an autocomplete menu that lists valid
formulas, names, and text strings. This is a great way to create and edit formulas while avoiding typing and
syntax errors.
● A fun way to learn new formulas is just by typing an equal sign and a single letter of the alphabet. Choose one of
the options that pops up and you will learn what that formula does.
 Mathematical Operators & Auto-filling

Mathematical operators
● The mathematical operators used in spreadsheet formulas include:
● Subtraction – minus sign ( - )
● Addition – plus sign ( + )
● Division – forward-slash ( / )
● Multiplication – asterisk ( * )

Auto-filling
The lower-right corner of each cell has a fill handle. It is a small green square in Microsoft Excel and a small blue square in Google Sheets.

● Click the fill handle for a cell and drag it down a column to auto-fill other cells in the column with the same value or formula in that cell.
● Click the fill handle for a cell and drag it across a row to auto-fill other cells in the row with the same value or formula in that cell.
● If you want to create a numbered sequence in a column or row, do the following: 1) Fill in the first two numbers of the sequence in two adjacent
cells, 2) Select to highlight the cells, and 3) Drag the fill handle to the last cell to complete the sequence of numbers. For example, to insert 1
through 100 in each row of column A, enter 1 in cell A1 and 2 in cell A2. Then, select to highlight both cells, click the fill handle in cell A2, and drag
it down to cell A100. This auto-fills the numbers sequentially so you don't have to type them in each cell.
 Referencing

Absolute referencing
● Absolute referencing is marked by a dollar sign ($). For example, =$A$10 has absolute referencing for both the
column and the row value
● Relative references (which is what you normally do e.g. “=A10”) will change anytime the formula is copied and
pasted. They are in relation to where the referenced cell is located. For example if you copied “=A10” to the cell
to the right it would become “=B10”. With absolute referencing “=$A$10” copied to the cell to the right would
remain “=$A$10”. But if you copied $A10 to the cell below, it would change to $A11 because the row value isn't
an absolute reference.
● Absolute references will not change when you copy and paste the formula in a different cell. The cell being
referenced is always the same.
● To easily switch between absolute and relative referencing in the formula bar, highlight the reference you want to
change and press the F4 key; for example, if you want to change the absolute reference, $A$10, in your formula
to a relative reference, A10, highlight $A$10 in the formula bar and then press the F4 key to make the change.
 Data Range & Combining Formulas with Functions

Data range
● When you click into your formula, the colored ranges let you see which cells are being used in your spreadsheet.
There are different colors for each unique range in your formula.
● In a lot of spreadsheet applications, you can press the F2 (or Enter) key to highlight the range of data in the
spreadsheet that is referenced in a formula. Click the cell with the formula, and then press the F2 (or Enter) key
to highlight the data in your spreadsheet.

Combining with functions

● COUNTIF() is a formula and a function. This means the function runs based on criteria set by the formula. In this
case, COUNT is the formula; it will be executed IF the conditions you create are true. For example, you could
use =COUNTIF(A1:A16, “7”) to count only the cells that contained the number 7. Combining formulas and
functions allows you to do more work with a single command.
 Spreadsheet Errors and Fixes

When you are new to data analytics and sometimes even when you aren't, spreadsheet struggles are real. It never feels
good when you type in what you are sure is a perfect formula or function, only to get an error message. Understanding
errors and how to fix them is a big part of keeping your data clean, so it’s important to know how to deal with issues as they
come up, and more importantly, not to get discouraged.

Strategies to avoid spreadsheet Errors

1. Filter data to make your spreadsheet less complex and busy.
2. Use and freeze headers so you know what is in each column, even when scrolling.
3. When multiplying numbers, use an asterisk (*) not an X.
4. Start every formula and function with an equal sign (=).
5. Whenever you use an open parenthesis, make sure there is a closed parenthesis on the other end to match.
6. Change the font to something easy to read.
7. Set the border colors to white so that you are working in a blank sheet.
8. Create a tab with just the raw data, and a separate tab with just the data you need.
Common Spreadsheet Errors and Examples
 Conditional formatting in Microsoft Excel

To set up conditional formatting in Microsoft Excel to highlight all cells in a spreadsheet that contain errors, do the following:

1. Click the gray triangle above row number 1 and to the left of Column A to select all cells in the spreadsheet.
2. From the main menu, click Home, and then click Conditional Formatting to select Highlight Cell Rules > More
Rules.
3. For Select a Rule Type, choose Use a formula to determine which cells to format.
4. For Format values where this formula is true, enter =ISERROR(A1).
5. Click the Format button, select the Fill tab, select yellow (or any other color), and then click OK.
6. Click OK to close the format rule window.