how to use data validation and conditional formatting in excel
TRANSCRIPT
![Page 1: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/1.jpg)
HOW TO USE DATA VALIDATION AND CONDITIONAL
FORMATTING IN EXCELTO CREATE A WORKSHEET CALENDAR
![Page 2: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/2.jpg)
WE WILL LEARN TO CREATE A SIMPLE EMPLOYEE CALENDAR LIKE THIS:
![Page 3: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/3.jpg)
HERE ARE THE STEPS TO CREATE SUCH AN EXCEL CALENDAR:
![Page 4: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/4.jpg)
STEP 1:
![Page 5: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/5.jpg)
STEP 2:
![Page 6: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/6.jpg)
STEP 3:
![Page 7: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/7.jpg)
STEP 4: HERE CLICK ON INSERT A DROP DOWN LIST ITEM
From the form controls menu at the Cell no. A1
![Page 8: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/8.jpg)
LIKE THIS:
![Page 9: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/9.jpg)
STEP 5:In the cell A1 where you have added the list,
right-click on Format Controls and click on the icon as shown in the picture:
![Page 10: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/10.jpg)
In the input range section add the months we’ve written before:
![Page 11: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/11.jpg)
In the cell link section add the cell no. A1
![Page 12: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/12.jpg)
STEP 6: ADD THE FOLLOWING FORMULA IN B6
![Page 13: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/13.jpg)
STEP 7:Go to Home > Number > lower arrow in
the right-hand corner > Custom and type in
the ‘type’ as this:
![Page 14: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/14.jpg)
STEP 8: Doing the previous step will add a
date in the cell, then to simply add the following dates in the next cell type in = B6+1 and drag to add in all the days.
To add the “Period from…” text use the following formula after merging the required number of cells.
![Page 15: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/15.jpg)
![Page 16: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/16.jpg)
If you find the same start and end date, then add the following last bit in the formula:
![Page 17: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/17.jpg)
Finally add a dash of colour to your calendar and use “conditional formatting” to highlight
the weekends:
![Page 18: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/18.jpg)
![Page 19: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/19.jpg)
SELECT THE COLOR FROM THE FILL TAB AND CLICK OKAY
Then from ‘manage rules’ option go conditional formatting rules and do as shown:
![Page 20: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/20.jpg)
TWEAK THE FORMULA SLIGHTLY AS:
![Page 21: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/21.jpg)
AND THERE YOU HAVE IT!
You now have a working Excel Worksheet calendar!
![Page 22: How To Use Data Validation And Conditional Formatting in Excel](https://reader031.vdocuments.us/reader031/viewer/2022012914/5873aee81a28aba3548b6e11/html5/thumbnails/22.jpg)
THANKS FOR WATCHING!
Pune
First Floor, Sinbhai Niwas, Plot No 382/2, Gokhale Road, Model Colony, Pune – 411016.(Landmark: Deep Bangla Chowk)Phone No. : +91 880 681 2444Phone No. : +91 206 541 2444
To know more about Advanced Excel training in Delhi
contact us at:Gurgaon (Head Office)
K-3/5, DLF Phase 2, Behind Central Arcade, Gurgaon 122 002, Delhi NCR.(Landmark: Sahara Mall, M. G. Road)Phone No. : +91 852 787 2444Phone No. : +91 124 450 2444 Email: [email protected]