← Data analytics

note 2 of 3

Pluralsight Excel

on this page · 16
  1. Formatting
  2. Custom Number Formats
  3. International Currency Formats
  4. Working with Data
  5. Outline Data
  6. Insert Subtotals
  7. Insert References
  8. Reference Data by Using Structured References
  9. Consolidate Data
  10. Configure Data Validation
  11. Analyze Data
  12. Look Up Data by Using the XLOOKUP Function
  13. Look Up Data by Using the XMATCH Function
  14. Power Query
  15. Import, Transform, and Connect to Data
  16. Combine and Display Data

Formatting

Discusses the importance of cell formatting for readability and data interpretation. Key topics include number formatting, cell styles, and custom formats.

Custom Number Formats

Use Cases

  • Specific number formatting (Date, Time, Decimals, Accounting, Percentage, etc..)
  • Data from csv file need to be formatted for analysis in 3rd party application (Leading Zeros).
Custom_Number_Format

International Currency Formats

Symbols for Different Currency

Accounting Formats (Does not do conversion) International Currency

Working with Data

Covers the basics of data entry, manipulation, and sorting. Emphasizes on efficient data management techniques.

Outline Data

Group Data to Create Outlines

Data > Outline > Group/Ungroup Outline Pre Outline Post

Insert Subtotals

Get Subtotal Amount for a Specific Group

Data > Outline > Subtotal
First sort list by criteria to subtotal by. Subtotal

Insert References

References Rows and Columns

  • Relative reference example: =sum(C6:F6)
  • Absolute reference example: =sum(G7*$F$3)
References

Reference Data by Using Structured References

Reference Data by Use of Table Names

  • Count of fields in table Sales: =COUNT(Sales)
  • Max Change of values in table Sales: =MAX(Sales[Change])
Structured Reference

Consolidate Data

Consolidating Data From Multiple Workbooks

Data > Data Tools > Consolidate
Consolidate

Configure Data Validation

Limit User Inputs

Data > Data Tools > Data Validation
Data Validation

Analyze Data

Extract Information from Data

Home > Analysis > Analyze Data

  • Create PivotTables, PivotCharts, Suggestions, etc..
Analyze Data

Look Up Data by Using the XLOOKUP Function

Successor to VLOOKUP, HLOOKUP, LOOKUP

Formulas > Function Library > Lookup & Reference > XLOOKUP

  • Find return value in same row
XLOOKUP

Look Up Data by Using the XMATCH Function

New Version of MATCH function

Formulas > Function Library > Lookup & Reference > XMATCH

  • Perform a lookup and return a horizontal or vertical position
XMATCH

Power Query

Explains how to use Power Query for advanced data import and transformation. Highlights include merging and cleaning data sets.

Import, Transform, and Connect to Data

Connect Data from Workbooks Using Power Query

Start with brand new workbook to import data to.

  • Data > Get Data > From File
  • Select Excel data file in Navigator and click Transform Data to open Power Query editor.
  • Choose relevant columns
  • Create connection by choosing Close & Load to
  • Get Data from another workbook: Data > Get Data > From File
Import Data 1 Import Data 2

Combine and Display Data

Use Power Query to Combine and Display Data

Data > Get Data > Launch Power Query Editor

  • Merge queries together and gather data into a single usable workbook.
  • Home > Combine > Merge Queries
  • Data > Combine Queries > Merge
Import Data 2