Complete this course to learn how to import and clean data, improve data entry with data validation, split and concatenate cell contents, work with data across worksheets, work with named ranges, and protect a worksheet. In addition, Excel's table-related features are discussed and PivotTables are introduced.
- Format cells and worksheets
- Select data
- Work with formulas and functions
Recording of a Previous Session
Description of the video:
Hello and welcome to Excel: Working With Data. In this course, you will be exploring more advanced topics about working with information in Excel, specifically, improving data entry with data validation, splitting and concatenating cell contents, working with named ranges, protecting a worksheet, cleaning source data, working with data across worksheets, combining and formatting worksheets, sorting and filtering data, and creating simple pivot tables. Before getting started, you'll want to make sure that you're familiar with the basics of working in Excel. This includes formatting cells and worksheets, selecting data, and performing calculations with formulas and functions. If you're familiar with those topics, then you're ready for Excel: Working With Data. Make sure you have Excel installed on your computer. As we go through the course, we will be referring to exercise files. You can download those from the download links included in each section of the course. Now it's time to get started with Excel: Working With Data.
Important note for participants not affiliated with IU
- This course contains training material only.
- Participants who are not affiliated with Indiana University will need your own access to installed and licensed software, according to the training topic. This will allow you to follow along and participate in the hands-on exercises.
- If you do not have access to the needed software, you can still enroll in the online course and read the course content.
More information about this course
This online course is free to everyone and available for enrollment through IU Expand.
This course consists of a series of short lessons, examples, demonstrations, practice quizzes, and hands-on exercises.
To complete the course content, you will need:
- a web browser and internet access to use the course material on IU Expand.
- a copy of the software relevant to the training installed on your computer.
Expand the accordion folds to see a detailed description of the content.
Explore the contents of Excel: Working With Data:
Open an Excel workbook.
View a summary of selected data on the status bar.
Learn about and create data validation rules.
Work with existing data validation rules.
Separate data in a single column into multiple columns.
Use column headers to create named ranges.
Combine two columns (First_Name and Last_Name) into a single column.
Copy the year from a date and place it in a new column.
Use named ranges in a function.
Explore how to protect a worksheet and then remove the protection.
Explore how delimited text files can be imported into Excel.
Remove blank rows, set consistent formats, and find and replace spaces.
Use a formula to calculate a running balance.
Explore ways to work with data in multiple worksheets, including copying and pasting data, correcting the #REF! error, and linking data between worksheets.
Use the worksheet tabs to place the worksheets in chronological order.
Select multiple worksheets and format these worksheets simultaneously.
Review the benefits and procedures for working with Excel tables.
Use the built-in filter in an Excel table to filter the data.
Review how to hide columns then learn how to select only the visible data.
Utilize the Remove Duplicates dialog box to remove duplicate data.
Use the Sort dialog box to sort the data alphabetically.
Learn about the structure and use of PivotTables as well as how to organize source data.
Create and format a simple PivotTable.
Explore how to filter and then pivot a PivotTable.