Preview

Data Analysis & Management

Data Analysis Categories

Data analysis is the study of various types of data and bringing together various pieces of data to form meaningful relationships. From those relationships, the Excel user can see patterns or trends by aggregating the data and creating visual representations of that data through charting or some other visual representation of the data.
Data Analysis Categories
1. Descriptive Analytics – what happened (Our Excel studies fall primarily in the Descriptive category)
    a. Data aggregation – What is it? Data aggregation is the process of gathering data and presenting it in a summarized format. Examples of aggregating data are Excel/Access Data tables, Excel Pivot tables.

1. Once data is gathered, it can be graphed in a chart for printing and/or used in a PowerPoint presentation.

     b. Data mining – sifting needed data from datasets

1. Creating Queries
2. Pulling data from databases
3. Tabulating the data and aggregating it through Data tables and Pivot tables.

 2. Diagnostic Analytics – why did it happen
     a. Regression analysis
     b. Filtering
     c. Time-series analysis 
3. Predictive Analytics – what is likely to happen in the future
     a. Regression analysis
     b. Machine Learning 
4. Prescriptive Analytics – what is the best course of action (the most complex)
    a. Algorithms
    b. Machine Learning
    c. Statistical Methods
    d. Computational Modeling

---

4-Steps to Data Analysis and Management

1. Importing data – Managing data begins with accessing the data, whether it is part of an Excel workbook or originally from another source such as SQL or Microsoft Access. That data can be imported into Excel easily using the Get & Transform Data section of the Data tab.
2. Check and clean data for conformity to Excel standards (best practices). Are there blank columns, rows, or cells? This should be corrected now. The Power Query Editor could be used as an option.
3. Sort the data using the various sort tools available on the Ribbon.
4. Add formulas/functions as desired. Creating charts from the data can assist in seeing patterns/trends.

Let’s begin with the first step; importing data.
1st Step: Use the Ribbon to import data into Excel from various sources including (Data tab – Get & Transform Data section):

• Comma Separated Values text files
• Microsoft Access tables
• Other Excel Workbooks

2nd Step: Basic Standards for Structuring and Cleaning Your Data in Lists, Ranges, Tables
In should be understood that none of these standards are required in Excel. These suggested standards should be followed when creating/storing data in Excel:

• No empty rows
• No empty columns
• Use Excel functions to appropriately edit your data
• The Power Query Editor is a viable option to use when cleaning and editing data.

3rd Step: Use the Ribbon to select various sorting tools. (Data tab – Sort & Filter section)

• Sorting icons on the Ribbon in multiple locations
• Sorting buttons in tables
• Sorting A to Z or Z to A
• Sort by cell distinction: color, icon, etc.

4th Step: Use formulas/functions as desired.

• Understand that you can use functions in the other steps also.
• Adding functions at the end of the 4-step process is better in terms of the functions executing properly with no surprise, unexpected, or erroneous data results.

---

Data Management Tools

​Use the Ribbon to import data into Excel from various sources including (Data tab – Get & Transform Data section):

• SQL tables
• Microsoft Access tables
• Website tables
• Other Excel tables

Excel Standards for Structuring Data in Lists, Ranges, Tables         
It should be understood that none of these standards are required in Excel. Think of these more in terms of Excel Best Practices.

• No empty rows
• No empty columns
• No data header titles in 2 or More Rows. Titles work best in a single row. This makes working with Excel data management tools easier
• Check numbers for added zero(s) to the end of the number

To check for empty rows/columns: select any cell in the range/list/table:

1. Press CTRL + A to highlight all of the data
2. Press CTRL + . (period); each depression moves active cell to each of the 4 corners of the database.
3. Perform a sort; lowest to highest value numerically or alphabetically. Doing this uncovers blank rows in your database. Sometimes the blank rows are shown at the end of the database so check there if you don’t see any blank rows at the beginning of the database

Use the Ribbon to select various sorting tools. (Data tab – Sort & Filter section)

• Sorting icons on the Ribbon in multiple locations
• Sorting buttons in tables
• Sorting A to Z or Z to A
• Sort by cell distinction: color, icon, etc.

END OF LESSON

---

Back to: Intro to Excel 2016 Data Analysis > Module 1 – Intro to Excel 2016 Data Analysis