Pivots are very powerful summarization (and more..) tool of Excel. There are many formatting options in Pivot that can provide insights (Ex. Subtotal, etc.)

Excel provides a default pivot format and its useful in most of the cases. But to customize it to our requirement, we need to change formatting and summarization levels. Unfornatuly, this needs to be done every time we create a Pivot.

What if we can change the default way in which excel formats and summarizes pivot and we can tell excel to use the format that we want every time we create a Pivot?

This short video explains the steps..

Formulas can easily get long and complex.. Multiple IFs, arrays and what not !

The joy of seeing the result that we want after pressing that enter or ctrl+shift+enter (for arrays) turns into horror when it results in an error and even worse is when we don't know which part of the formula is resulting in this error !!

Excel has a neat way to tell us what it is checking and which part is giving out an error - Step-By-Step :)

Explained in this short video with a simple example..

I am sure those who update excel sheets with many rows or columns or across multiple sheets would find this useful !

This is about how to "clone" an excel file, look at different rows, columns or sheets of the same file in multiple windows and make changes, save them as a single file !

I know, this could be confusing on a casual read, but couldn't think of a better one yet :)

Easier once you see this short video :)

When there are millions of rows and columns that are supposed to have formulas, how to check if there are any manually entered values in the place of formulas ? Re-run the formula ? Wait for hours for the formula to fully run ? And check what is the change in values ?

There is always a better way of doing things !

Simple clicks is all that is required to know the needle in the haystack !

Explained in this short video..

Most of our excel files are huge, with many sheets and links and we as auditors know that devil is always hidden :)

Few years back when we were investigating a fraud and recovered an excel file from suspects laptop, it had large size but not much of sheets or rows or columns and no hidden sheets by checking through the "regular" right click on sheet name etc...

But we used a different method to identify "VeryHidden" excel sheet, which had goldmine of information through which we cracked the fraud !

Explained in this short video..

This is useful when we are required to lookup values for a value range.. ex. Purchase Order (PO) approver based on PO value 

Explained in this short video..

This one function would can all other separate functions for getting sum, count, average, max, min.... !

Explained in this short video..

An interesting function to include only working days and excluding weekends and holidays !

Pro Tip : You can select which days of the week you can exclude using a neat trick ! 

Explained in this short video..

An interesting function to exclude pre-defined weekdays and list of holidays.

Pro Tip : You can select which days of the week you can exclude using a neat trick !

Explained in this short video..

Lets say you want to hide few sheets from the recipients (not making it so obvious for unhide) or hide all sheets, below 3 methods (actually 2 :)) can be used.

Method 1 - Our easy to Hide and Unhide option | Right click on file name and hide or unhide.. Yes, it does not fall strictly under "not making it so obvious for unhide" category :) Nevertheless, added it here for completeness sake, you never know the excel skill level of the recipient !

Method 2 - This is a good one that i had learnt few years back.. It uses VB window (no, not a macro !)

(BONUS : It you want the file to be never unhidden (aka very confidential !) , you can use "Protect Workbook" after hiding the sheet !)

Method 3 - I saw an excel file with no sheets few years back and was surprised how it can be done ! It was indeed very simple to do that !

All 3 methods explained briefly in this short video :)

Of the many methods, this is probably the quickest !