Google+
anything in Excel...

It's Excel-o-lozy...

Trick to count number of sheets / tabs in #MSExcel file / workbook without using VBA / Visual basic formula solution

www.Excelolozy.com - Without using VBA editor you can use this trick to count sheets in Excel file. In case you delete few sheets, VBA window won’t give true number of sheets.
So the trick is as follows:
> Select first sheet
> type in a blank cell “=CountA(”
> click on cell “A1”
> before hitting enter key hold down Shift key and click on last sheet
> note: formula reference would change.
> now hit enter.
Formula to Count sheets in Excelolozy by Vikrmn CA Vikam Verma author 10 Alone Chartered Accountant worksheet workbook file
 Formula to Count sheets in Excelolozy by Vikrmn CA Vikam Verma author 10 Alone
 Chartered Accountant worksheet workbook file 

Anything in Excel, be an Excelolozian...
for best MS Excel Tips visit http://www.Excelolozy.com
gotta query??? drop a mail at mail@excelolozy.com

Posted by Vikrmn: Author of "10 Alone" (CA Vikram Verma) 10Alone.com

Avoid Excel 2010 file Save As error #Excelolozy due to document not saved for long; repair hours of unsaved rework.

www.Excelolozy.com - Sometimes when you work on a document for long hours and forget to save the file then an error pops up that says due to prolonged unsaved work excel is unable to save document.
Excel asks to repair the file but it is not possible till the file is saved.

There are two following methods to rescue your work:

  1. Save workbook as "Excel Binary File" as ".xlsb", or
  2. Save workbook as simple "Excel 97- 2003 file" as ".xls"
In second case above it'll ask for some formatting confirmations as it's saving the file in old format from updated one i.e Excel 2010 or 2007 to 2003. Check all options and save.
Now last thing to be done is to open ".xls" or ".xlsb" file and Save As ".xlsx".

Voohoo. Job done. No late night stretch at office.. Cheers.. Go Home early.. ;)


Excelolozy for Excel tips and tricks by Vikrmn author 10 Alone CA Vikam Verma
Excelolozy 
 Anything in Excel, be an Excelolozian... for best MS Excel Tips visit http://www.Excelolozy.com 
Gotta query? Drop us a mail at mail@excelolozy.com  
Posted by Vikrmn: Author of "10 Alone" (CA Vikram Verma) 10Alone.com

#Execelolzy - Transpose formulas without changing reference - Copy Paste Trick - relative or fixed reference - Column to Row - errors in MS Excel

www.Excelolozy.com - MS Excel changes reference while Copying formulas from horizontal cells (rows) to vertical cells (columns). Transpose command under paste special has this error.

Following are two easiest methods to do it:

  1. Absolute reference trick - Slow method 
  2. Replacing "=" (Equal sign)  trick - Fast method 

These are elaborated as follows:

  1. Absolute reference method: 
    • Edit all cells in range by inserting a dollar sign in front of each row and column reference.
    • It makes all cells as absolute cell references.
    • It will keep formulas static.
    • Now copy the range and paste at destination cell/s through > Paste > Paste Special and now then check the box next to Transpose and it's done.

       
  2. Replacing "=" (equal sign):
    • Select the range to be transposed. 
    • Press Ctrl+H and replace "=" (Equal sign) with any sign say "#" (Hash). 
    • Click Replace All. 
    • It converts the formulas to text.
    • Copy the selected data. 
    • Paste the data through Paste > Paste Special by checking Transpose option.
    • The data is convert from Row to Column or vice versa.
    • Now replace "#" with "=" using Replace All button in the find and replace box.
 Voila.. It's done.. Enjoy.. Cheers.. 


Excelolozy for Excel tips and tricks by Vikrmn author 10 Alone CA Vikam Verma
Excelolozy 
 Anything in Excel, be an Excelolozian... for best MS Excel Tips visit http://www.Excelolozy.com 
Gotta query? Drop us a mail at mail@excelolozy.com  
Posted by Vikrmn: Author of "10 Alone" (CA Vikram Verma) 10Alone.com

How to apply more than one Auto filter on different data ranges in an Excel Sheet?

Excelolozy.com - Applying auto filter on a database in a sheet is pretty simple. But how to apply autofilter if there are two different set of database tables in a sheet?

It's simple. Select the first data range.
Go to > Home > Styles > Format as table.
Then select the second Data rage and do the same there too. Now you can apply filter on these two data ranges separately.

Apply more than 2 auto filters in a sheet at #Excelolozy by Vikrmn CA Vikam Verma
Apply more than 2 auto filters in a sheet at #Excelolozy by Vikrmn CA Vikam Verma

Moreover any number of data ranges can be set as a table in a worksheet and thus auto filter be applied separately on all of these.

How to apply filter in single click shortcut of three buttons.. Read here..

Anything in Excel, be an Excelolozian...
for best MS Excel Tips visit http://www.Excelolozy.com
Gotta query??? Drop a mail at mail@excelolozy.com
Posted by Vikrmn: Author of "10 Alone" (CA Vikram Verma) 10Alone.com

Excel Formula to convert Date from to valid date format like YYYYMMDD to DDMMYYYY - an update

www.Excelolozy.com - "Formula Fix" was created to Convert YYYYMMDD to DDMMYYYY i.e. the Date from Raw text format to recognisable and valid Date format.

Now it works on a single digit year format too.

Following was the Formula (read here full post):

formula is "ConvertDate"

=ConvertDate(Ref,OldFormat)

This tool used to work with a two digit year like "98" means 2008.

As per need of an Excelolozy user we have upgraded it to Single Digit year format too.

i.e. it now converts  "8" as "2008".


The formula "ConvertDate" is available within tool named "FormulaFix".

"FormulaFix" is a "Formula (fx) Fixing Tool" that provides various formulas that don't exist in Excel.

To download "FormulaFix" click here.


Examples of formula "ConvertDate" are as follows :


Raw DateOld FormatFormulaConverted Date resultRemarks
20121013if omitted=convertdate(A2,)Saturday, October 13, 2012YYYYMMDD is taken by default if omitted in the formula.
13102012DDMMYYYY=convertdate(A3,B3)Saturday, October 13, 2012Any type of OLD FORMAT can be converted to any type of NEW FORMAT.
322012DMYYYY=convertdate(A4,B4)Friday, February 03, 2012
2313DMYY=convertdate(A5,B5)Saturday, March 02, 2013
2032013DMMYYYY=convertdate(A6,B6)Saturday, March 02, 2013
2013 03 2YYYY MM D=convertdate(A7,B7)Saturday, March 02, 2013
20130302YYYYMMDD=convertdate(A8,B8)Saturday, March 02, 2013
03-02-2013MM-DD-YYYY=convertdate(A9,B9)Saturday, March 02, 2013Any character of OLD FORMAT can be omitted by placing any character in NEW FORMAT.
13aaa3aaa02YYxxxMxxxDD=convertdate(A10,B10)Saturday, March 02, 2013
13bbb3bbb02YYxxxMxxxDD=convertdate(A11,B11)Saturday, March 02, 2013



To see practical application of above table do as follows :
1. Download formulaFix.
2. Copy paste above table (with headers) at any new excel workbook at cells range A1.

See the magic ;)

Anything in Excel, be an Excelolozian...
for best MS Excel Tips visit http://www.Excelolozy.com
gotta query??? drop a mail at mail@excelolozy.com

Excel addict turns Novelist..

10 Alone : authored by Vikrmn: CA Vikram Verma
10 Alone : a Novel by Vikrmn
www.Excelolozy.com - Excel is my passion. It helped me a lot to keep the progress track of my Novel "10 Alone". More than that i also used Macros (though in Word) to repetitive changes accross all my chapters in my novel.

But penning down a novel doesn't mean i won't create any new tool for Excel.
I would keep on doing it.. as writing is my passion.. be it writing programming language or Macro codes or fiction.

I ll be always available for Excel queries.

Cheers
Vikrmn:

Anything in Excel, be an Excelolozian...

for best MS Excel Tips visit http://www.Excelolozy.com

gotta query??? drop a mail at mail@excelolozy.com

Best password in Excel file

www.Excelolozy.com - Suppose a protected file is to be opened in front of anyone or unknown prying eyes. The moment you enter the password file is exposed.

Q: Is it possible to avoid this?
A: Yes, avoid entering the password in front of anyone.

Q: Any other thought or say better idea?
A: Probably NO.

..but.. Imagine for a sec..

Imagine that you enter the Password in front of anyone but still your file is safe.

How is this possible?

I may be talking alien..
BUT.. It's possible.. :) :)

I am talking about an "Un-Hackable Password"  

..but.. HOW??

For that.. wait and watch..

checkout my next post..


Anything in Excel, be an Excelolozian...

for best MS Excel Tips visit http://www.Excelolozy.com

gotta query??? drop a mail at mail@excelolozy.com

Keyboard error shift + 2 is ' rather than @ (number help)

www.Excelolozy.comWhen Shift key is held down and 2 is pressed then it shows ‘ (single quote) rather than @ (at).

This happens with all number keys that don’t work in combination with shift key. 

Reason:
This happens due to language settings of keyboard that is set as UK or some other one, instead of US.
  
Solution:
To solve this go to :
Control Panel > Regional settings > Key board settings > Change the keyboard from UK to US > then select all others Key board languages and click “Remove”.


Anything in Excel, be an Excelolozian...

for best MS Excel Tips visit http://www.Excelolozy.com

gotta query??? drop a mail at mail@excelolozy.com