exel additional practices-with_groups

Microsoft Office Excel 2007

Content

Methodology

GROUP 1 Convert text to columnsApply quick styles to tablesModify picturesUse Count FunctionCopy a seriesHide and show rows and columnsUse advanced formulasMove and resize graphic objects

GROUP 2Summarize data using functions (average)Rules for conditional formattingCustom format of the cellDelete duplicated data within a tableChange the size of rangesRestrict data using data validationApply the feature for compatibilityApply quick styles to tables (design)

GROUP 3Find data using a formulaVlookup and Hlookup functionsCalculate subtotalsMark workbooks as finalSave a book with enable macrosFormat painterSelect sources of data

GROUP 4Use the document inspectorInsert, modify and delete commentsSort data using criteriaAdd and remove graphic elementsCopy and move spreadsheets to another workbookChange the type of chartsFilter and sort dataView tab

GROUP 5Allow multiple usersUse Functions CountIfDraw shapesFormulas with absolute referencesShow and print formulasUse topics to give format to spreadsheetsProtect workbooks and sheetsSplit screen

Methodology•Each slide has a color box that corresponds to a topic to develop, focused on a particular objective.•Topics are developed with specific exercises in each slide. They are included in a .zip folder with this presentation.•Finally, there is a set of steps to graphically follow the use of the tool.

Color box : It mentions the certification topics that are covered in that slide.Objective box :

It explains the required objective and emphasizes the learning of the certification topics.

Preview box :You will find a quick visual guide about the topic to easily locate the exercise to be done.

Certification exercises box : Sometimes you will find a link to a practical exercise with the data you need for reviewing what you know.

Home button : Press this button to return to the topic index.

Convert text to columns

Objective:

Split the content of cells into different columns

Certification exercises:

Use practice 11 for Excel

Approximate time: 10 minutes

Convert text to columns1.- Data tab2.-Data Tools group3.-Text to columns4.-Follow the wizard

Preview

Apply quick styles to tables

Objective:

Apply table format




Format a table with a style1.- Home tab2.- Styles group3.- Format as Table

Preview

Modify pictures

Objective: Change the way that pictures are viewed

by changing attributes, size, etc.




Preview

Pictures format1.-Select the picture2.-Picture tools tab3.-Picture Styles group4.-Picture Effects5.- Glow

Use the Count function

Objective:

Calculate how many cells contain values in a range of cells




Count Function1.- Formulas tab2.-Functions Library group3.-Insert Function3.-Count

Preview

Copy a series

Objective:

Create a series of data, numbers, arrows or lists




Copy a series1.-Home tab2.-Editing group3.-Series

Preview

Hide and show rows and columns

Objective:

Hide or show rows and columns to better view the work area




Hide and show1.- Home tab2.- Cells group3.-Format

Preview

Use advanced formulas

Objective:

Determine the state of a specific condition or the answer to a logical question




IF function1.- Formulas tab2.- Function Library group3.- Logical

Preview

Move and resize graphic objects

Objective:

Resize pictures




Size of the graphic objects1.- Select the graphic object2.- Picture Tools tab3.- Size group3.- Height and Width

Preview

Summarize data using functions (average)

Objective: Excel includes a broad range of

functions for different uses, we will learn how to use them with parameters




Average function1.- Formulas tab2.-Function Library group3.-Insert Function3.-Select Statistical4.- Average5.-Select several ranges

Preview

Conditional formatting rules

Objective:

Analyze the data in a spreadsheet under different criteria.




Conditional Formatting1.- Home tab2.-Styles group3.-Conditional Formatting

Preview

Custom format of a cell

Objective:

Create special cell formats to apply them to cells




Cell format1.- Home tab2.- Number group3.-Number arrow4.- Custom[Blue]0 “ Text”

Preview

Eliminate duplicated data within a table

Objective:

Learn to filter a list of data by removing the duplicated data




Remove duplicates1.-Data tab 2.-Data Tools group3.-Remove Duplicates

Preview

Change the size of the ranges

Objective:

Modify the default ranges without changing their names




Change range1.- Formulas tab2.-Defined names group3.-Name manager

Preview

Restrict data using data validation

Objective: Restrict the type of data that can be

entered into a cell to provide integrity




Restrict access to data1.- Data tab2.- Data Tools group3.- Data Validation

Preview

Apply the feature for compatibility

Objective: Learn how to save files that are

compatible with previous Excel versions




Apply compatibility features1.- Office button2.-Save As3.-Excel 97-2003 Workbook

Preview

Apply quick styles to tables (design)

Objective: Learn how to create tables to manage

data in them




Quick styles1.- Select the table2.- Table Tools tab3.- Table Styles group4.- Select the style

Preview

Find data using a formula

Objective:

Set the values to perform a sum based on criteria




Sumif function1.-Formulas tab2.- Function Library group3.-Select Insert Function4.-Select SUMIF

Preview

Vlookup and Hlookup functions

Objective:

Learn how to find a value in a cell when it is stored somewhere else




VLOOKUP function1.- Formulas tab2.- Function Library group4.-Select Insert Function5.-Select VLOOKUP

Preview

Calculate the subtotal of data

Objective: Learn how to organize large groups of

data in more manageable groups




Subtotals1.- Data tab2.-Outline group3.-Subtotal

Preview

Mark workbooks as final

Objective: Mark a document as final and make it

read-only to avoid changes




Mark as final1.- Office button2.-Prepare3.-Mark as Final

Preview

Save as Macro enabled workbook

Objective: Learn how to save workbooks that run

macros when opened




Save files with enabled macros1.- Office button2.-Save As3.-Excel Macro-Enabled Workbook

Preview

Format painter

Objective: Apply existing formats to other cells

using the Format painter feature




Format painter1.- Home tab2.- Clipboard group3.- Format painter tool

Preview

Select appropriate sources of data for charts

Objective:

Learn how to choose the type of chart for your data to make it meaningful to your audience




Choose the appropriate chart1.-Insert tab2.-Charts group3.-Columns

Preview

Use the document inspector

Objective: Check the document before sharing it,

be sure it does not have hidden or personal information before sharing it




Documents inspector1.- Office button2.-Prepare3.-Inspect document

Preview

Insert, show, modify and remove comments

Objective:

Learn how to insert comments or notes to specify information related to a cell




Comments1.-Review tab2.-Comments group3.-New Comment

Preview

Sort data using one or several criteria

Objective:

Data can be sorted using one or several criteria (columns)




Sort data by several criteria1.- Home tab2.-Editing group3.-Sort &Filter button4.-Custom Sort5.- Sort on field

Preview

Add and remove chart elements

Objective:

Change the format of the parts in a chart manually




Chart format1.- Select the chart2.-Chart Tools tab3.-Labels group4.- Axis Titles

Preview

Copy and Move spreadsheets to another workbook (Spreadsheet format)

Objective: Excel workbooks include 3

spreadsheets. The user may add, remove, move and copy the spreadsheets he or she wants.

Certification exercises:Use practice 22 for Excel 22 B


Organize spreadsheets1.-Right click on the spreadsheet tab2.-Select the option you want from the menu

Preview

Change the type of chart

Objective:

Give different aspects to 2D and combined charts




Change the aspect of charts1.-Select the chart2.-Chart Tools tab3.-Type group4.-Change Chart Type

Preview

Filter and sort data using cells attributes

Objective:

Sort data through their cell attributes (colors)




Sort data by attributes1.- Select the cells2.-Press the arrow at the right of the column to be filtered3.-Select Filter by Color4.- Select the indicated option

Preview

View tab

Objective:

Learn how to hide or show elements




Gridlines1.- View tab2.-Show/Hide group3.-Gridlines

Preview

Allow that multiple users make changes to workbooks

Objective:

Learn how to share a workbook




Share a workbook1.-Review tab2.-Changes group3.-Share Workbook4.-Turn on the check box

Preview

Use the COUNTIF function

Objective:

Count the cells that match specific criteria




Preview

COUNTIF function1.- Formulas tab2.- Function Library group3.-Select Insert Function4.-Select COUNTIF

Draw shapes

Objective:

Use shapes to highlight important information




Basic shapes1.- Insert tab2.-Illustrations group3.-Shapes

Preview

Create formulas that use absolute and relative cell references

Objective: Avoid that the reference to a cell

changes when copying or moving it, the method might be full (Absolute) or just in rows or columns (Mixed)




Absolute references1.- Select the cells in the formula2.-Press F4 to make the value absolute3.-Press F4 again for mixed values

Preview

Show and print formulas

Objective:

View all the formulas on screen to audit them




View formulas on screen1.- Formulas tab2.-Formula Auditing group3.-Show Formulas

Preview

Use themes to format spreadsheets

Objective: Apply themes to give a standard look in

colors, fonts and effects




Themes1.-Page Layout tab2.-Themes group3.-Themes

Preview

Protect workbooks and spreadsheets

Objective:

Protect documents to avoid that a user changes, moves or deletes important data




Preview

Protect workbooks and spreadsheets1.- Review tab2.-Changes group3.- Allow Users to Edit Ranges

Split the screen horizontally

Objective:

Split the spreadsheet to view different sections




Preview

Split the screen1.- View tab2.- Window group3.- Turn on or off the button to view different sections of the spreadsheet.

exel additional practices-with_groups

Technology

data tab2

excel approximate time

certification exercises

data tools group3

series of data

excel columns approximate

table format use practice

data validationgroup