Welcome to MSDN Blogs Sign in | Join | Help

Microsoft Excel

The team blog for Microsoft Excel and Excel Services.
Video Tip: Conditional Formatting and Data Validation

Today's author: Sam Radakovitz, a Program Manager on the Excel team that enjoys creating VBA applications in Excel.

A bit ago, Joseph had a blog post about community clips on OfficeLabs.com, so for this post, I wanted to take advantage of the video demonstration and do the post via community clips. The topic is an Excel tip about using conditional formatting and data validation to turn on and off the formatting on the sheet without needing VBA code.  The video can be found here and is also embedded below.

The workbook I used is attached to this blog post if you want to play around with it, and since you can't really see the formulas I used in the video, I'll post them here and add them to the comments of the video:

Data Validation formula:

=options

Conditional Formatting formula:

=IF($B$2=$A$13,1,0)

This was my first attempt at a video and I had some video problems around the screen capture failing on me and the microphone volume. I'll work to fix those issues if I do this again. I'm curious to hear from folks if they like the video post, or would prefer to keep things in text, or a mix of both. Thanks!

Posted: Friday, September 05, 2008 1:47 PM by Joseph Chirilov
Attachment(s): CFandDV.xlsx

Comments

funny wallpaper » Video Tip: Conditional Formatting and Data Validation said:

PingBack from http://housesfunnywallpaper.cn/?p=4331

# September 5, 2008 4:57 PM

hanan said:

At the conditional formatting formula,you can write only:

=b12=a13

# September 6, 2008 12:51 AM

Eric said:

I don't mind videos, but, to me, the content of this one doesn't really fit the technical nature of the blog.

# September 6, 2008 1:28 AM

David said:

Video seems to be the wave of the future on the Internet, but it involves a much bigger commitment from the reader/viewer. I can skim a text entry in a couple of seconds and move on, but I may have to watch 30 seconds or more of a video to determine if it's worthwhile to me. In fact, with video I might sit through a couple minutes and find I didn't learn anything, while I could have covered a couple dozen blog entries or more in the same time.

# September 6, 2008 8:54 AM

Sofa_Inferno said:

Thanks for the Tip, nice effect and easy to manage! Maybe it is woth mentioning for foreign users that one has to adjust the formula(s) according to their language version of Excel (wonder who came up with that Idea, it really scews things up sometimes and English is so international!)

For us German users it is =wenn(B$2$..... instead of =if(B$2$....

I also like the way you presented the tip, although i can understand the concerns of David. A video-only presentation might be a bit too little but maybe with a text that contains the basic ideas behind the task (maybe like a to-do-list) it would help the more advanced Excelists not to waste their time.

I prefer videos so I can follow the instructions step by step and can see the onscreen output in realtime. In addition to the example file provided for download it worked just perfect for me.

# September 10, 2008 11:13 AM

Dan said:

Keep it text and on topic for the blog.  Not everyone in the world has high-speed internet and not every location allows streaming video.  Agree with David that video is a MAJOR waste of time unless the content somehow requires a demonstration.

# September 10, 2008 11:26 AM

Ron de Bruin said:

Hi Sam

Thanks for the tip and video<g>

This is working for every language version

=$B$2=$A$13

# September 14, 2008 7:03 AM

SamRad said:

Thanks for the comments!  Some replies:

Hanan

Thanks, no need for the if … I’m so used to doing it for otherthings its just embedded in my head now.

Eric

Thanks, we’ve posted other non-super technical stuff and it went over ok, maybe this tip isn’t really techy enough though.

David

Good points overall, videos can be more time consuming.

Sofa_inferno

Good points on the international text, I’ll have to watch out for that.  At least it worked when you opened the file :-)

Dan

True on the high speed internet point, just trying something different here since I’ve seen folks happy with other video blogs and video sites in general.  If I ever do another video blog, I’ll be sure to include a full text write up with it.

Ron

Thanks for the formula, realize now it should have been that way :-)

# September 22, 2008 7:48 PM
New Comments to this post are disabled
Page view tracker