Managing and formatting data

Introduction to Excel

Jess Ahmet

Curriculum Manager, DataCamp

Ways to manage data

  • Data stored and managed in tabular layout
    • Columns and rows in all sheets
  • Create named ranges
  • Create Subtotals
  • Data validation

Example of a manually entered table of data within Excel with headers

Introduction to Excel

Named ranges

  • Name a range of columns or rows
  • Benefits include:
    • Easier to reference range in formulas
    • Less time searching sheets for important ranges of data
  • The Name Manager feature allows you to:
    • Edit named ranges
    • Create news named ranges
    • Deleted named ranges

Image showing the features in Excel related to naming ranges of cells.

Image showing the Name Manager window in Excel that allows users to edit, create, and delete named ranges within their data.

Introduction to Excel

Subtotals

  • Sense check numerical data through calculations
  • Can help spot large anomalies early on
  • Subtotal creates aggregation within the dataset
    • Sum
    • Count
    • Average
  • Can also add a grand total of the column

Image of the Subtotal button that is found in Excel.

Table of student test results, with a count by gender added using the subtotal feature

Introduction to Excel

Validating data

  • Useful when sharing workbooks
  • Data Validation allows user to control the values entered into a cell
    • Including the data type
  • Can add input messages to inform user of data validation settings
  • Customize error message if user enters invalid data

Screenshot of the Data Validation button in Excel

Example of a data validation rule that has been set on the test score column of a sample dataset, where only values between 0 and 100 are accepted.

Introduction to Excel

Formatting data

  • In Excel, you can format whole sheets, individual cells, or specific values
  • Examples:
    • Font size
    • Sheets with borders around all cells
    • Values matching their data type (i.e., currencies)
  • Conditional formatting
Introduction to Excel

Custom formats

  • Many available formats for different data types
  • Custom section also available

Screenshot of the test results data, with the test_date column selected and the format being changed to a custom format

Introduction to Excel

Conditional formatting

  • Conditional Formatting allows users to set conditions for highlighting cells within the data.
  • Helps identify patterns or trends
  • Formatting options include:
    • Icons
    • Data bars
    • Highlighting cells using color scales
  • Can create, edit, and delete rules through the Manage Rules window.

Introduction to Excel

Let's practice!

Introduction to Excel

Preparing Video For Download...