Split excel in to several sheets based on column data?

While working on yearly reports or data you have for several users. You have the consolidated data in to several sheets based on year or user names. This can be easily achieved by macro. Please find below an example and a macro to achieve this.

Replace zero with blank in excel

In financial sheets and calculation we want a utility to replace zero with blanks. Don't worry this can be achieved with a macro.

Find and replace enter keys in excel

Most of the time while working excel we accidently end up typing Enter keys. We can easily find and replace the enter or Alt+Enter keys in excel cells.

Press Crtl+H

Now in Find text - Press Alt Key on your keyboard and type 0010 on numpad. Remember when you are typing on numpad keep the Alt key pressed. i.e, "Alt0010". You wont see any character in Find text.

Now keep replace with as blank

Press Find All to find the Alt+Enter Keys or Replace all to replace or remove them.

Handle error formulae in excel

Most of the time when we perform any calculation using formulaes and they return #Value or #N/A which we don't want to return instead we need to display blank or 0 values. We can do this by using following formulae.

To display blank when the formulae returns error "=iferror([original formula which you want to perform],"") "

To display 0 when the formulae returns error "=iferror([original formula which you want to perform],"0") "

Example 1: =iferror(Sum(A1:A14),"")

Example 2: =iferror(Sum(A1:A14),"0")

Add secondary axis to chart

If your graph has 2 columns with numeric data and looks like below chart.

The above chart is difficult to analyze, because the scale of the Transactions is much larger than the scale of the Units Sold. As a result, we can barely read the Units Sold series and can’t gain any useful insight from the virtually indistinguishable columns. The Total Transactions should be moved to a secondary axis, allowing the series to be scaled differently.