Curso de Excel passo a passo - Aula 09

Welcome to the Excel Course

Introduction to the Lesson

• The instructor welcomes students to an Excel course, emphasizing hands-on practice with real spreadsheet challenges.

• Today's lesson will be extensive, divided into four sessions to avoid overwhelming students and includes additional exercises.

Preparing for the Exercise

• Students are informed about a pre-prepared exercise file named "Exercício, página 019," located in the class spreadsheets folder.

• If preferred, students can create the spreadsheet from scratch by referring to page 19 of their textbook.

Calculating Student Grades

Initial Calculations

• The instructor guides students on summing grades for each student starting from cell G2 using cell references.

• Excel highlights cells as they are referenced, aiding in visual tracking during formula input.

Repeating Calculations Efficiently

• A student inquires about repeating calculations without retyping; the instructor suggests practicing cell references first for confidence before exploring shortcuts.

Inserting Missing Data

Adding a New Column

• To insert missing third-term grades, students are instructed to right-click on column F and select 'insert.'

• After entering new grades, it’s noted that total formulas need adjustment since they only sum previous terms.

Adjusting Formulas

• The instructor explains that while order of operations doesn’t affect results here, maintaining clarity is essential for future troubleshooting.

Handling Errors in Excel

Deleting Columns and Resulting Errors

• A student decides to delete the second-term column but encounters a #F error indicating a broken reference due to deletion.

• The instructor clarifies that this error arises when a formula's referenced data is removed and emphasizes understanding prior references for resolution.

Resolving Formula Errors

• To fix errors after deleting columns intentionally or accidentally, one must manually adjust each affected formula or use the 'replace' function.

Using Replace Functionality

Streamlining Error Correction

• The instructor demonstrates how to use Ctrl + U for quick access to replace errors across multiple formulas efficiently.

Finalizing Corrections

• After replacing all instances of errors with nothing (effectively deleting them), students check their formulas again and find everything functioning correctly.

Excel Undo and Redo Commands Explained

Understanding the Undo Command

• The undo command in Excel allows users to revert up to 100 actions, accessible via the top left corner of the window or by using the shortcut Ctrl + Z.

• Users are encouraged to practice using Ctrl + Z to return a column to its previous state, demonstrating the functionality of this command.

• Important considerations for the undo command include:

• If a file is closed, reopening it will disable the undo function for that session.

• The undo command does not work if a worksheet is deleted.

• When multiple files are open, actions undone in one file may affect others.

Utilizing the Redo Command

• If too many actions are undone, users can click on the redo button located at the top of Excel or use Ctrl + Y as a shortcut. Caution is advised when using these commands rapidly.

Finalizing Work in Excel

• Before concluding an exercise, users should ensure their spreadsheet includes all necessary data and totals calculated correctly.

• Two additional exercises are provided for further practice, available in a designated folder labeled "Exercícios Extras," which contains relevant files for review.