Ministry of Education Capacity Development Center Day 3

Apply Conditional Formatting Conditional formatting allows you to automatically apply formatting—such as colors, icons,

Highlight Cells Rules • Select the cells • Home tab->Styles group • Conditional Formatting->

• Type 40 at Greater than text box and click ok • Highlight

• Type 5 at Top 10 Items text box and click ok •

Clear Rules Click Home tab ->Styles group->Conditional Formatting-> Clear Rules, Clear Rules from Selected

Consolidating Data • To summarize and report results from separate worksheets, you can consolidate

• Create East. xlsx and West. xlsx for sales product from January to

• Create a new workbook with Master. xlsx • After making Arrange Vertical

• Add Cell ranges from East and West workbooks to All references box

Find and Replace • Click Home tab->Editing group-> Find&Select 3/13/2019 CDC-4 17

• Type Director at Find what and Managing Director at Replace with 3/13/2019

Filtering and Sorting Data • Select the cells range you want to sort •

• Choose Expand the selection at Sort Warning 3/13/2019 CDC-4 20

• Select the cells range you want to filter • Click Home tab->Editing

Assignment • Assignment 4 • Create New Worksheet, • Enter staff data (18/20) format

Assignment 5 • Create Attendance sheet, • filtering greater than 75% Assignment 6 •

Slides: 26

Download presentation

Ministry of Education Capacity Development Center Day 3: Apply Conditional Formatting Dr. Aye Sandar Myint Assistant Director 3/13/2019 CDC-4 1

Apply Conditional Formatting Conditional formatting allows you to automatically apply formatting—such as colors, icons, and data bars—to one or more cells based on the cell value. 3/13/2019 CDC-4 2

Highlight Cells Rules • Select the cells • Home tab->Styles group • Conditional Formatting-> Highlight Cells Rule-> Greater Than 3/13/2019 CDC-4 3

Highlight Cells Rules 3/13/2019 CDC-4 4

• Type 40 at Greater than text box and click ok • Highlight the cells with red color 3/13/2019 CDC-4 5

Top/ Bottom Rules 3/13/2019 CDC-4 6

• Type 5 at Top 10 Items text box and click ok • Highlight the cells with red color 3/13/2019 CDC-4 7

Adding Data Bars 3/13/2019 CDC-4 8

Adding Color Scales 3/13/2019 CDC-4 9

Adding Icon Sets 3/13/2019 CDC-4 10

Clear Rules Click Home tab ->Styles group->Conditional Formatting-> Clear Rules, Clear Rules from Selected Cells. 3/13/2019 CDC-4 11

Consolidating Data • To summarize and report results from separate worksheets, you can consolidate data from each into a master worksheet. • The worksheets can be in the same workbook as the master worksheet or in other workbooks. • When you consolidate data, you are assembling data so that you can more easily update and aggregate as needed. 3/13/2019 CDC-4 12

• Create East. xlsx and West. xlsx for sales product from January to March West. xl sx East. xls x 3/13/2019 CDC-4 13

• Create a new workbook with Master. xlsx • After making Arrange Vertical for three workbooks, Click Data tab->Data Tools group->Consolidate at Master 3/13/2019 CDC-4 14

• Add Cell ranges from East and West workbooks to All references box • Click Ok 3/13/2019 CDC-4 15

3/13/2019 CDC-4 16

Find and Replace • Click Home tab->Editing group-> Find&Select 3/13/2019 CDC-4 17

• Type Director at Find what and Managing Director at Replace with 3/13/2019 CDC-4 18

Filtering and Sorting Data • Select the cells range you want to sort • Click Home tab->Editing group->Soft & Filter->Sort A to Z 3/13/2019 CDC-4 19

• Choose Expand the selection at Sort Warning 3/13/2019 CDC-4 20

• Select the cells range you want to filter • Click Home tab->Editing group->Soft & Filter->Filter • Choose a product or products at Text Filters and Click OK 3/13/2019 CDC-4 21

3/13/2019 CDC-4 22

Assignment • Assignment 4 • Create New Worksheet, • Enter staff data (18/20) format 3/13/2019 CDC-4 23

Assignment 3 3/13/2019 CDC-4 24

Staff Data 3/13/2019 CDC-4 25

Assignment 5 • Create Attendance sheet, • filtering greater than 75% Assignment 6 • Create Exam Mark Sheet, • Sorting , filtering with criteria Assignment 7 • Open Staff Data sheet of assignment 4 • sorting and searching with criteria 3/13/2019 CDC-4 26