My Small World

(in continuation of my earlier post: " Custom Validation in Excel ") Today I seriously recognised the power of data validation. I was stuck in a situation wherein I had to create a template wherein the user needs to enter a date, however it the date cannot be a Saturday or a Sunday. After a brief "R&D", I realized using "data validation" was the apt option. Go to Data -> Data Validation In the dialogue box which pops up, choose "Custom" from the drop down under Settings -> Validation Criteria -> Allow In the new field "Formula", just enter the criteria you want to achieve. In my case, I used "=WEEKDAY(A2,2)<6" Then, you may click on the "Error Alert" tab and may put any customized error alert like the picture below: So, now the desired cell is validated and user can only enter a weekday. Another, popular validation that I often use is the "List" wherein one can easily cr

Excel 2007 onwards, playing around with Pivot Table has become even more user friendly. Pivot was seemingly a tool preferred by advanced users of excel but today its a child's play :) Extended options over right click has made this a must use tool for all users who are required to handle a reasonable amount of data. For eg: "Summarize Values By" on right click gives you the option to summarize data by sum or count or average or to choose from all available options. Earlier, one had to be conversant with the options to actually use it. "Show Values As" on right click allows to apply calculations to the data area. Microsoft is making more and more features available for average / less than average users who actually are keen to use the features. Lets all of us make use of this. Note: In case my readers want, I can explore on writing a basic step by step guide on basics of Pivot Table!