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

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

"Sumif" not calculating right values in the formula.

www.Excelolozy.comSometimes the formula Sumif or Sumifs doesn’t calculate right values.
The reason of error can be the criteria for which value is to be calculated.

Problem:
Check if following characters are being used as text in your data..

'*' or '<' or '>'

e.g.
“>10” if used in criteria as text then it won’t capture right totals.

Diagnose:
It happens because Excel considers these wild characters as operators as ‘Operators’.

Solution:
1.       Either replace these characters with some Alpha-numeric values
-          e.g. “Greater than 10”
2.       Use these characters at any place other than the first place in the text
-          e.g. “10<”
3.       Insert space before that character
-          e.g. “ <10”

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 Formula to convert Date from to valid date format like YYYYMMDD to DDMMYYYY under formulaFix by Excelolozy.

www.Excelolozy.com - It's now easy to Convert YYYYMMDD to DDMMYYYY i.e. the Date from Raw text format to recognisable and valid Date format.

Following is the Formula :

formula is "ConvertDate"

=ConvertDate(Ref,OldFormat)


The formula "ConvertDate" is available within tool named "formulaFix".
 
"formulaFix" is a "Formula 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

Formula to convert date from Raw text format to valid date format?

www.Excelolozy.com - Converting YYYYMMDD to DDMMYYYY i.e. the Date from Raw text format to recognisable Date format... It is always time consuming, and tough.

e.g. To convert date format from YYYYMMDD to DDMMYYYY a little long combination of 2-3 formulas is required.

To convert raw date format directly to system recognisable date format, following formula works wonders :
formula is "ConvertDate"

=ConvertDate(Ref,OldFormat)

where...
 
Ref is Reference of the cell where Raw data is entered.
and OldFormat is the Format in which the raw data exists.


an Example is hereunder...
  
if Raw date in Cell A1 is 20121013

Now 2 options are there...

1) If it is written in YYYYMMDD format... 

then formula should be...
 
=ConvertDate(A1,"YYYYMMDD")

or

=ConvertDate(A1,)

even this will work...

because default format is YYYYMMDD...

Result would be "13-Oct-2012".


2) If it is written in DDMMYYYY format...

then formula should be...

=ConvertDate(A1,"DDMMYYYY")

and Result would be "20-Dec-1013".
 
 
Amazing... isn't it???

Now what this "ConvertDate" formula is all about? How to use it? How to integrate it with Excel...

Click here to know more... ;)
 
 

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

Press Ctrl + F to search Life at Work

www.Excelolozy.com


How many of us search life at work?

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

Column Filter with "Float feature" and "Saving filter options for future use"...

www.Excelolozy.com - Column filter has been updated with further unique features...
 
Column filter is a tool that can filter columns (Horizontally) like Auto Filter does for rows (Vertically).
  
Latest version of Column filter has following features :  

  • Saving filter options for future use (Maximum 9 filters can be saved for future use).
  • Float feature where Filter form floats on the sheet i.e. worksheet at the background can be clicked.
  
New feature of "Alphabetical ordered" list will be introduced soon...
 
"ColumnFilter" is available for download 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

Column Filter with 2 new features...

www.Excelolozy.com- Column filter has 2 more features now...
 
Column filter is a tool that can filter columns (Horizontally) like Auto Filter does for rows (Vertically).
 
Latest version of Column filter has following features :


  • Apply recently used filter
  • Check and Uncheck All items to be filtered
 
New feature of "Alphabetical ordered" list with "Search options" will be introduced soon...
  
"ColumnFilter" is available for download 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

Excel vLookup formula Limitations are resolved.. under formulaFix by Excelolozy.

www.Excelolozy.com - "avLookup" is the formula with advanced features of vLookup.

vLookup in Excel has a limitation to search the data from first column only.

"avLookup" has following 2 features :
  • It can search from any column
  • Search column can exist even after the Result column.

The formula is :
=avLookup(LookupValue, RangeRef, SearchColumn, ResultColumn, ExactMatch)

Where

  • LookupValue - Value to be searched
  • RangeRef - Table reference
  • SearchColumn - Number of Column where above value is to be searched from
     - It's optional, can be omitted, by default it is 1
     - i.e. it'll search 1st column of table if nothing entered
  • ResultColumn - Number of Column where result is to be picked from
    - It's optional, can be omitted, by default it is 2
    - i.e. it'll search 2nd column of table if nothing entered
  • ExactMatch - 0 means Exact value to be searched, 1 is for nearest value
    - It's optional, can be omitted, by default it is 0
    - i.e. it'll search Exact match if nothing entered

Magic is that search Column can be after Result Column.

The formula "Comment" 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.

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

vLookup - Value Not In First Column... Solution...

www.Excelolozy.com - Ever thought of any solution to the problem where vLookup should search the value in any column rather than first column.

Formula vLookup has a problem that it searches the value in First column only and gives value from any column you need the result from.

Solution is there...

avLookUp() which means "AllSideLookup"... ;)

Click here for solution to vLookup Limitations.
 
 

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 Formula to copy the Comment of one cell in another cell as text.. under formulaFix by Excelolozy.

www.Excelolozy.com - A bit typical but valid and important question.

Ever felt the need of a formula to get the "Comment" of one cell as "Text" in another cell.

Following is the Formula :

=Comment("A1")

Strange!!! No?

Yup, 

It brings the Comment of any cell or say Range "A1" as a TEXT in the another cell where the formula is entered.

This formula is miracle for them who want to apply some sort of formula based on contents of Comment of a cell.

The formula "Comment" is available within tool named "formulaFix".

"formulaFix" is a "Formula (fx) Fixing Tool" that provides various formulas that dont exist in Excel.

To download "formulaFix" click 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