Chapter 22 - Introduction to Data Analytics with Excel

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/33

encourage image

There's no tags or description

Looks like no tags are added yet.

34 Terms

1
New cards

Uses of Excel in Marketing

Budgeting, data analysis, A/B testing, and customer segmentation[cite: 1].

2
New cards

Uses of Excel in Sales

Forecasting sales, reporting sales, commission calculations, and managing inventory[cite: 1].

3
New cards

Uses of Excel in Accounting

Budgeting and forecasting, data entry and recording, account reconciliation, tax planning and compliance, and audit trail and documentation[cite: 1].

4
New cards

Uses of Excel in Banking and Finance

Financial modeling, financial reporting, portfolio management, market analysis, options pricing, and loan amortization[cite: 1].

5
New cards

Uses of Excel in Medicine

Research and data analysis, patient tracking, clinical studies, and billing and finance[cite: 1].

6
New cards

Uses of Excel in Science

Data analysis, data visualization, modeling and simulation, chemical analysis, environmental monitoring, and genetics analysis[cite: 1].

7
New cards

Bar Graph

A chart often used to compare multiple items over a period of time or when comparing different items[cite: 1].

8
New cards

Line Graph

A chart used to compare changes that have occurred over time[cite: 1].

9
New cards

Pie Chart

A chart used to compare different parts of a whole, for example, showing budget expenditures[cite: 1].

10
New cards

Chart Elements

A title gives context, axes identifiers show what is displayed on the X and Y axes, data labels display the actual entry for each point, and a legend identifies colors, lines, bars, or pie segments[cite: 1].

11
New cards

Excel Data Analysis Tools

Pivot tables, sorting and filtering, conditional formatting, and Lookup functions[cite: 1].

12
New cards

Data Cleanup Tasks

Removing duplicates, removing special characters, trimming spaces, and validating data[cite: 1].

13
New cards

Financial Color Conventions

Black is positive (credits, profits) and red is negative (debits, losses)[cite: 1].

14
New cards

Traffic Light Color Conventions

Green depicts safe or low risk, yellow depicts increased risk, and red depicts unsafe or high risk[cite: 1].

15
New cards

Conditional Formatting

Highlights or changes the appearance of cells based on criteria that you set, often used to make data outside certain parameters easily noticed[cite: 1].

16
New cards

Named Range

Assigns a meaningful name to a specific range of cells, adding clarity and simplifying the process of referencing data in complex spreadsheets[cite: 1].

17
New cards

Structured Referencing

Referencing data elements in a table by their column names instead of traditional cell references[cite: 1]. They automatically expand as the table grows or shrinks[cite: 1].

18
New cards

Date Conversion Handling

When importing dates, Excel handles them through date format recognition, date auto-correction, and custom date formatting[cite: 1].

19
New cards

Logical Functions

Functions like IF, AND, OR, and NOT that make decisions based on criteria, acting as a True/False question[cite: 1]. IF is used for comparisons and returns two results[cite: 1].

20
New cards

Advanced Date Functions

DATE creates a date specifying year/month/day, DATEDIF calculates the difference between two dates, and NETWORKDAYS calculates the number of working days between two dates[cite: 1].

21
New cards

Pivot Table

Used to summarize, analyze, and present complex data[cite: 1]. Valuable for handling large datasets and extracting insights by dynamically rearranging data[cite: 1].

22
New cards

VLOOKUP

A function that references a cell to match values from an array of data arranged in columns[cite: 1].

23
New cards

HLOOKUP

A function that references a cell to match values from an array of data arranged in rows[cite: 1].

24
New cards

XLOOKUP

A function that references data in rows, columns, or tables and can search left or above the reference cell[cite: 1]. It provides wildcard entries and closest matches[cite: 1].

25
New cards

INDEX and MATCH

MATCH searches an exact column or row to find a position, and INDEX returns a cell's value within an array[cite: 1]. Together, they overcome VLOOKUP's limitation of only looking to columns on the right[cite: 1].

26
New cards

What-If Analysis

Allows you to explore different scenarios by changing input values to observe how they impact calculations[cite: 1]. Main tools are Data Tables, Scenario Manager, and Goal Seek[cite: 1].

27
New cards

Scenario Manager

A What-If analysis tool used to save, manage, and compare different sets of input values when multiple variables can influence outcomes[cite: 1].

28
New cards

Data Tables

A What-If analysis tool used to see how changing one or two input variables affects the final result of a formula[cite: 1].

29
New cards

Goal Seek

A What-If analysis tool used when there is a specific target and you want to work backward to find the input value to achieve it[cite: 1].

30
New cards

Forecast Sheet

A tool that creates forecasts and visualizes future trends and seasonal variances based on historical data[cite: 1].

31
New cards

Power Query

An Excel Power Tool used to connect data sources, import, clean, shape, and transform data for analysis[cite: 1].

32
New cards

Power Pivot

An Excel Power Tool for data modeling, creating relationships between tables, and performing advanced calculations using Data Analysis Expressions (DAX)[cite: 1].

33
New cards

Power BI

A business intelligence tool used to craft personalized dashboards and interactive reports for data visualization[cite: 1].

34
New cards

Microsoft Copilot in Excel

An AI tool that streamlines writing complex formulas, providing intelligent code suggestions, auto-completion, and contextual assistance for data analysis[cite: 1].