Microsoft Excel

How to Extract PDF to Excel Free

2014-02-26T06:02:00.000-08:00

Adobe’s Portable Document Format or simply PDF is unavoidable and necessary in today’s age of electronic file sharing, storing and exchanging. When you want to send an Excel table or chart via email, file sharing service, or upload it to the Internet, you will most likely save it as or print to PDF (depending on your preferred way of creating PDF files) first to preserve its formatting, styles and layout, and to make sure that people you are sharing the spreadsheet with can open and view it no matter what device or operating system they are using.

For all its usefulness and uniformity, PDF is of no use when you need to correct your calculations, update data or budget projections. What you need is the original or source XLS(X) spreadsheet, which you don’t necessarily have. Since there is a solution for most computer and file management problems, there is one for recovering spreadsheet data locked in the PDF. It is called PDF to Excel converter software. PDF to Excel conversion tools come in many flavours: desktop, mobile and online apps, paid or, fortunately, free. If your are an Excel buff who is looking for a PDF to Excel tool that is -

● free,

● reliable,

● accurate,

● quick,

● easy to use,

● safe

● with no maintenance costs, and

● without taking toll on your computer resources

- then go ahead and check out Free PDF to Excel Conversion Online. It satisfies all of the above conditions. Plus - it does not have limitations on the frequency of use or the number of converted files.

To demonstrate how easy to use it is, let’s see what it takes to get your PDF translated to fully a editable and customizable Excel spreadsheet.

1. Upload the PDF: Click the button Choose File to browse and select the file from your computer.

2. Input email address: specify the mailbox where you want to receive the message that your file is converted, along with a link to download the resulting XLS file.

3. Send for conversion: click the button Start! to dispatch it.

These three steps are virtually all that it takes. The provider translates your PDF and returns it in a formatted XLS file via a download link sent to your inbox. As soon as it is downloaded, it can be opened and edited in Excel, or Calc (for OpenOffice and LibreOffice users).

How To Change "Page Headers" For Different Pages In MS Word Document

2014-01-21T10:54:00.000-08:00

This is article explains, How to change "page headers' for different pages in MS Word.

1. Let say, you have opened a word document having the content in 3 pages.

2. Go to Page 1 of the Word document

3. Enter Header as "SARAN KUMAR AND ASSOCIATES" by following any of the below steps

Double click the "Header" area

Click "Edit Header" in the Drop Down command of "Header" in the Group "Header & Footer" under the "Insert" Tab which is from the Ribbon Bar

4. By default the same Header (ie., SARAN KUMAR AND ASSOCIATES) appears in all the 3 pages

5. To change the Header name as "WWW.CHAIRETURN.COM" for the 2nd Page, continue the following steps:

Go to end of the 1st Page (previous page of the proposed 2nd Header)
Click "Next Page" in the Drop Down command of "Breaks" in the Group "Page Setup" under the "Pay Layout" Tab which is from the Ribbon Bar (this is to add the Section Break)

Go to the 2nd Page and double click the Header

Deselect the "Link to Previous" command in the Group "Navigation" under the "Header and Footer Tools - Design" Tab which is from the Ribbon Bar

Enter the Header as "WWW.CHAIRETURN.COM" and which will simply continues for the following pages

6. To change the Header name as "WWW.ONEPERSONCOMPANY.IN" for the 3rd Page, continue the following steps:

Go to end of the 2nd Page (previous page of the proposed 3rd Header)
Click "Next Page" in the Drop Down command of "Breaks" in the Group "Page Setup" under the "Pay Layout" Tab which is from the Ribbon Bar (this is to add the Section Break)

Go to the 3rd Page and double click the Header

Deselect the "Link to Previous" command in the Group "Navigation" under the "Header and Footer Tools - Design" Tab which is from the Ribbon Bar

Enter the Header as "WWW.ONEPERSONCOMPANY.IN" and which will simply continues for the following pages

Let us know, your comments below.

Google Online Calculator - Discover more with Google Search

2013-07-20T14:15:00.001-07:00

Google is part is our life now. I can't imagine my life without google, these days especially.

"One of my friend is telling that most of the people opens google once just to check whether internet has connected or not". This itself is telling how our life dependent with the Google.

Though Google has lots of products, Search Engine is always the best.

Just a minute back I typed (12^5)*2 in Google search engine box just like that and I know that google displays the direct result like below.

But surprisingly, a calculator appears in google and displaying the result.

Wowww.. no need to open calc or excel every time whenever I need to do some calculations.

If you just want calculator to be displayed, just type calculator in the google search.

Discover more with Google Search here

IFNA Function - New logical function in Excel 2013

2013-01-29T11:34:00.000-08:00

Finally MS Excel 2013, is available in the market. Along with the wonderful features like Flash Fill and Quick Analysis Tool there are lot of new features are enabled in Excel 2013.

Apart from the new features, Excel 2013 released new functions also. Here we are going to discuss about the new Logical Functions in Excel 2013 those are IFNA & XOR.

In this post we will concentrate on the function IFNA, which is only available from Excel version 2013 and above.

IFNA Function:

Syntax: =IFNA(value, value_if_na)

IFNA returns the value you specify if the formula returns #N/A error value, otherwise returns the result of the formula

The name actually suggesting the same description. In simple words, if the result is #N/A then the result is value_if_na, if not then the result is value.

We can have better understanding through the example.

Let say, I am using VLOOKUP function to look up the marks of the students from master marks list.

In the above example, for the third student Excel resulted error (#N/A) since Krishna is not there in the master list.

If we want to further use AVERAGE function to the resulted marks, even the excel results as #N/A since one or more of the inputs are errors.

So, IFNA formula is to replace the error (#N/A) with any of the desired value.

Look here, the same example by IFNA Function

If you observe above, all the results are same from the above example except for the third student. Here, IFNA function replaces the #N/A error with the value 0.

AVERAGE function also results the correct value, as there is no error in the range now.

In next post, I will explain how XOR function works.

(IFNA Formula in excel 2013, how IFNA formula works in excel 2013, what is IFNA formula, new IFNA formula in excel 2013)

How to customize Quick Access Toolbar in MS Excel

2012-09-17T10:06:00.001-07:00

Microsoft Excel has different parts. The Ribbon, Office Button, Quick Access Toolbar, Menu Group, Formula Bar, Address Bar, Status Bar, Headers, Vertical Scroll Bar, Horizontal Scroll Bar and so on.

Each part of excel has its own importance. Let me discuss one of the Important part of Excel, that is Quick Access Toolbar.

And also, we will see what are the new options in Excel 2013 relates to the quick Access Toolbar

What is Quick Access Toolbar

Quick Access Toolbar is an independent toolbar, which is allowing user to add his favorite commands/controls for quick access.

Quick Access Toolbar is exactly like Tool Box.

Let say, I frequently use Comma style, Pivot Tables, Define Name, Data Validation options in Excel. But each of these options are in different Ribbon tabs. So, to quickly access these options I put all these options in Quick Access Toolbar.

We can feature Quick Access Toolbar either above the Ribbon or below the Ribbon, that is purely based on our convenient. But, the default location of Quick Access Toolbar is the upper left corner, next to the Office Button (above the Ribbon). (In Excel 2013 version - default position is the upper left corner above the Ribbon)

How to Move Quick Access toolbar from one location to the another location?

Right Click on the Quick Access Toolbar or any option in the Ribbon.
Select Show Quick Access Toolbar above the Ribbon / Show Quick Access Toolbar below the Ribbon

How to add favorite commands to Quick Access Toolbar?

Right Click on the Quick Access Toolbar or any option in the Ribbon and Select Customize Quick Access Toolbar

Click Office Button, Select Excel Options and Select Customize (in Excel 2007)
Click Office Button, Select Options and Select Quick Access Toolbar (in Excel 2010)
Select File from Ribbon, Click Options and Select Quick Access Toolbar (in Excel 2013)

Select All Commands under Choose commands from drop down
Then we can find all the options in MS Excel
Select any of the favorite option
Click Add
Like that, we can add all the required controls
Click OK

Then we can able to find those options in Quick Access Toolbar

Once we Click Reset, Excel delete all customizations and reset default settings for Quick Access Toolbar.

Keyboard Shortcuts to Quick Access Toolbar controls

The other bigger advantage of the Quick Access Toolbar is that each control in Quick Access Toolbar has allotted one unique shortcut.

It is not very difficulty to remember these shortcuts.

The shortcuts order is Alt and 1, Alt and 2, Alt and 3, Alt and 4 ..... Alt and 09, Alt and 08, Alt and 07 ...... Alt and 0A, Alt and 0B, Alt and 0C .....

(Note: Alt and 1 means Click Alt and release, then click 1)

Let say, I have added 4 controls to Quick Access Toolbar 1) Save 2) Undo 3) Redo 4) Comma Style.

Then the short cuts allotted to these four controls are Alt and 1, Alt and 2, Alt and 3, Alt and 4 respectively.

What are the new options for Quick Access Toolbar in MS Excel 2013

Reset All Customizations

Reset Only Quick Access Toolbar - Excel delete all the customizations and reset default settings for Quick Access Toolbar. (this option same as Excel version 2007 and Excel version 2010)

Reset All custiomizations - Excel delete all customizations from Quick Access Toolbar as well as in Ribbon Tabs and reset default settings

Export or Import Customizations

Export All Customizations - We can export current Ribbon and Quick Access Toolbar customizations into a file that can be used and imported into the other computer

Import Customization file - Import any exported Ribbon and Quick Access Toolbar customizations to include in the desired Excel file

Amazing Flash Fill Feature In Excel 2013 - How It Works? (10 examples) - (Topic 1/2)

2012-09-14T13:04:00.000-07:00

Finally, the preview version of MS Office 2013 is out. Obviously, we would be more interest in visiting our lovely MS Excel 2013 preview. You can download the MS Excel 2013 preview from here. See the snaps of MS Excel 2013 here.

Since I heard about Flash Fill feature in MS Excel 2013 much earlier, immediately after opens the Excel preview I first tested Flash Fill feature. After experienced the Flash Fill option performance in Excel 2013, I can simply say that the trailer is bumper hit !!! One scene of Flash Fill Option in the trailer is sufficient to make the movie super hit.

You may not believe that I worked almost 10 hours on Flash Fill option and worked out almost 100 examples. I am going to share 20 examples through Flash Fill articles. In this article I cover almost 10 important examples.

Without wasting any further time, I am jumping into the topic Flash Fill feature in Excel 2013.

What is Flash Fill Feature?

Flash Fill is one of the option available in Excel 2013 to fill the data automatically in values, based on the example you want as output.

Flash Fill saves time in writing formulas to extract part of data from a single column or combine data from two/more columns. Flash Fill identifies the first result which you provide and based on the that result it fills the rest of Cells.

We can find Flash Fill option

Data - Data Tools group - Flash Fill
Home - Editing group - Fill drop down - Flash Fill

Short cut to apply Flash Fill

Ctrl + E

Exercise # 1 - Extract First Name without Formula

I will start with the simple example

Let say we have the below data from Cell A1 through Cell A11

If we want to extract the first name of the Excel MVP from Column A, then we should follow the below steps in Excel version 2010 or below.

1. Enter the below formula in Cell B2

=LEFT(A2,SEARCH(" ",A2))

2. Then drag the formula till Cell B11

3. Finally, we get the result of First Name in Column B in Cell B2:B11

To get the final outcome, we should know two Text Functions names Left and Search. Then only it is possible to extract the first name.

However, there is a alternative way to split the first name and last name by using text to columns. But to use Text to column option, we should understand the inside options of Text to column.

Now, see how Flash Fill do your work in seconds of time.

Simply give first result in Cell B2 (that is Mike)

Just enter the second result first character in Cell B3, Flash Fill option displays all the results in gray color.

Once we click Enter, Flash Fill just fills the First Name in all the Cells.

Wow, wonderful !!!

This is just sample, we are going to see most wonderful examples below.

Note: Instead enter the first character of result in Cell B3, we can directly click Flash Fill option from the menu bar (menu ribbon) or use Shortcut Ctrl+E, Excel fills the results.

Exercise # 2 - Extract Last Name

Let say we have the same data as above in Column A.

And our target is to extract the Last Name of the data

Then in other Excel versions below 2013, you can use the below formula to extract the Last Name.

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

So, here we should have the knowledge on Text Functions names Right, Len and Search

But, in Flash Fill options.. simply fills the result.. hope no is explanation required here.

Just enter the first result in Cell B2 (Alexander)

Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.

Or Type the first character of the second result in Cell B3, then Excel Flash Fill option displays all the results in gray color. Just click Enter.

Exercise # 3 - Extract First character of First Name and Last Name

Let say we have the same data as above in Column A

Our target is to extract the First character of First Name and Last Name (M Alexander)

Use the below formula in Excel version 2010 and below in Column B.

=LEFT(A2,1)&RIGHT(A2,LEN(A2)-SEARCH(" ",A2)+1)

Oh my god! we have to know four Text Formulas to get the result (Left, Right, Search and Concatenate/&)

See how our wonderful Flash Fill feature fills below

Flash Fill feature in Excel-2013 is really so clever. Based on the first result which we have entered in Cell B2, it analyzes and gives you the remaining results in fraction of seconds.

Steps to get the results :-

Just enter the first result in Cell B2 (M Alexander)

Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.

Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for us. Simply click Enter.

Exercise # 4 - Result in the format Last Name, First Name

Let say we have the same data as above in Column A

Now our target is bit difficult. We want the result should be in Last Name, First Name format.

Based on the difficulty, we should use some complex formulas.

Use the below formula in Excel version 2010 and below, in Column B to get the result.

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&", "&LEFT(A2,SEARCH(" ",A2))

This formula is really crazy. How normal data entry operators has this much formula knowledge to apply. Here, we have to know four text formulas to get the result (Left, Right, Search and Concatenate/&)

In this way, Flash Fill saves thousands of hours of time for the guys who works with data entry/analysis/research.

Ok coming back to the topic, Lets see how Flash Fill gives us the result.

I hope, expectations has been changed, after seeing this result.

If we notice, Flash Fill not only extract a part from the data but also intelligently adds to the result, what we have added in the first result.

Since we added Comma (,) in between the first name and the last name, Excel Flash Fill option also added Comma (,) in the result and displayed before us.

Steps to get the result

Just enter the first result in Cell B2 (Alexander, Mike)

Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.

Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for us. Simply click Enter.

We will see, whether excel only adds special symbols like Comma (,) or adds any other value/text.

Simply Superb !!

I adds, MVP in between last name and first name and excel did the same thing.

I can simply say, Flash Fill is a logical Mirror of first result.

You have to tell how the Flash Fill option is?.. by giving comment below the post.

Don't thing that this is end. I am going to explain you further more examples. Ready to see brilliant examples than this.

Exercise # 5 - First letter of First Name and First letter of Last Name

Let say we have the same data as above in Column A

Now, we will test the Excel logically. We will ask Excel to fill First letter of First Name and First Letter of Last Name

From this example I not going to explain the formulas in Excel version 2010 and below, the reason that we may miss the focus in Flash Fill option. But note that the formulas should be so complex to solve from this Exercise.

See, how Flash Fill do our duty with our using any formula.

Excel did it !!!

No words to explain you more, except going to the another example.

As formality, find the steps below

Just enter the first result in Cell B2 (MA)

Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.

Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for you. Simply click Enter.

Exercise # 6 - First letter of Last Name / First letter of Fast Name

Now, we will ask Excel in a reverse way means First Letter of Last Name and First Letter of First name.. not Ok, we will ask to add Slash (/) Symbol in between.

The first result should be A/M

What not !

What not !!

What not !!!

What not !!!!

What not !!!!!!

Exercise # 7 - Extracting First 3 Characters of the First Name

If we want to extract the first 3 Characters of First Name.

See below, how Flash Fill performs these tasks.

Finally,

follow the below steps to apply Flash Fill feature in Excel - 2013.

Just enter the first result in Cell B2 (Alexander, Mike)

Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.

Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for you. Simply click Enter.

This is not the end. We will see some amazing examples in the Part - II of the Flash Fill article.

Next article on Flash Fill covers, Extracting data from dates, complex numbers, formatted numbers/text, combining of two or more data fields etc.,

How is Excel Flash Fill Option?

Do you like Flash Fill feature in MS Excel 2013, then share your experience by giving comment below.

Few Snaps From Microsoft Excel 2013 - So Tempting !!

2012-09-13T08:57:00.002-07:00

Microsoft recently released the preview version of Office 2013. I have taken few snaps from Microsoft Excel 2013. The colors are so tempting and the Excel it self some posh look in 2013 version.

Below are some snaps of Microsoft Excel 2013

Please click any pic and view in dynamic view for better clarity

Immediately after you open Excel 2013

Once you click the Blank workbook

Bottom Ribbon

Worksheet Boxes

Headings appearance - Cell Selection

Excel Top Right Corner

Formula Bar and Address Bar

Office Background - Circle and Stripes

Office Background - Calligraphy

Office Background - Circuit

Office Background - Clouds

Office Background - Straws

Office Background - Tree Rings

Excel Icon after saving a file

Learn About TRUNC Formula In Excel

2012-09-11T09:08:00.001-07:00

TRUNC formula is one of the Maths formula. TRUNC formula is one of the important formula for finance professionals. Especially, if you are working with any financials statements preparation, you would appreciate TRUNC formula.

TRUNC Formula

Full form of TRUNC is TRUNCATION

TRUNC formula, truncates a number to an integer by removing the decimal or fractional or part of the number.

Syntax of TRUNC formula is

TRUNC(number,num_digits)

Where as,

Number is the number which you want to truncate
num_digits is a number specifying the precision of the truncation.

Excel takes Zero (0) by default, if you don't give the num_digits

Example 1:

=TRUNC(19.2962,0)

TRUNC formula returns the result 19

=TRUNC(19.9962,0)

TRUNC formula returns the result 19

You have to notice from the two examples that TRUNC formula least bothered about rounding off.

If you give instruction that you want 0 decimals, TRUNC formula removes all the decimals and present before you. If you instruct to display only two decimals, TRUNC formula removes all the decimals after second decimal in the number (ex: =TRUNC(19.2299,2) = 19.22)

Example 2:

Let us see the results, by changing number of degits

1. =TRUNC(195,829.62852,4) =195,829.6285

Trunc formula removes all the decimals except the first 4 decimals.

2. =TRUNC(195,829.62852,3) =195,829.628

Trunc formula removes all the decimals except the first 3 decimals.

3. =TRUNC(195,829.62852,2) =195,829.62

Trunc formula removes all the decimals except the first 2 decimals.

4. =TRUNC(195,829.62852,1) =195,829.6

Trunc formula removes all the decimals except the first 1 decimals.

5. =TRUNC(195,829.62852,0) =195,829

Trunc formula removes all the decimals

6. =TRUNC(195,829.62852,-1) =195,820

Trunc formula replace the immediate digit to the left of the decimal point with 0

7. =TRUNC(195,829.62852,-2) =195,800

Trunc formula replaces the immediate two digits to the left of the decimal points with 0's

8. =TRUNC(195,829.62852,-3) =195,000

Trunc formula replaces the immediate three digits to the left of the decimal points with 0's

9. =TRUNC(195829.62852,-4) =190000

Trunc formula replaces the immediate four digits to the left of the decimal points with 0's

* Same results with -ve sign appears, if you try to truncate the -ve number

=TRUNC(-195,829.62852,-4) = -190,000

Wow.. My Pic In Google Search Beside My Posts - How Is It ?

2012-09-09T09:16:00.002-07:00

I am so excited to see my pic in google search beside my posts.

Since I am not a tech guy, I have been taken 2 days to get my pic in google search beside my blog posts.

After little research online, finally I can able to see my face on google search. I just want to share the same to my blog readers.

Please note that I am explaining from blogger point of you, since I am using blogger service.

Steps to follow to get your pic on google search beside your blog posts

Update google plus profile settings
Update About me page in your blog
Update the blog html

1. Update google plus profile settings

* Open your google plus page

* Click profile icon from the left side bar

* Click Edit Profile

* Click your profile pic to change your profile pic (if are ok with present pic please ignore this step)

* Select a photo from your computer

* Click Set as profile photo

* Click Contributor to update your blog name

* Add custom link

* Give label as your blog name and enter your blog url in the specified boxes

* Save

* Click Done editing beside your profile name

2. Update About me page in your blog

* Go to your blogger blog

* Click Pages (to add / update about me page)

* Add new page, if you don't have about me page earlier.

* If your blog has already about me page click edit.

* Write some thing about you (by giving your name)

* Now go to HTML mode, and replace your name with the below code

<a href="G+ URL" rel="author">YOUR NAME</a>

* Replace "G+ URL" with your google plus url and replace YOUR NAME with your name

* Click Update

3. Update the blog html

* Click template from the left side bar

* Click Edit HTML

* Click Proceed

* Add the below code just below the <head> eliment

<link href='G+ URL' rel='author'/>

* Replace G+ URL with your google plus url

* Click Save Template

Once you are done with all the above steps, don't rush to see immediately in the google search. Because google takes minimum 24 hours to update your pic in the google search beside your posts.

So, have a good sleep to see your pic next day.

Know 5 Useful Terms In MS Excel - 1 (Cell, Active Cell, Cell Reference, Input Cell, Merged Cell)

2012-09-08T01:23:00.000-07:00

My intention is very clear by starting Know 5 Useful Terms in MS Excel series that everyone should know the meanings of Important Excel terms.

I will try my level best to give you at least one post in this series, in every month. So that by next year you will be in a position to learn 60 Excel Important terms (not only meaning but also some interesting things)

So, lets opening the door for Know 5 Useful Terms In MS Excel series...!!!!

The 5 important terms in this article are

Cell
Active Cell
Cell Reference
Input Cell
Merged Cell

It makes sense, if we learn all related terms in one topic.

1. Cell :-

A box formed by the intersection of a Row and Column in a worksheet, in which you enter information.

In Excel 2007 & 2010, the number of Rows are 1,048,576 and number of Columns are 16,384.

So, the number of Cells in Excel (2007/2010) are 1,048,576 * 16,384 = 17,179,869,184

More than 17 Billionnnnnnn...... yes, that is the power of Excel.

2. Active Cell :-

The selected Cell in which data is entered when you begin typing. Only one cell is active at a time. The Active Cell is bounded by a heavy border.

Even Excel has Billions of Cells, we can not enter the data at a time in all the Cells. It is possible to enter data only in one Cell at a time. That Cell is nothing but the Active Cell at that time.

3. Cell Reference :-

The set of coordinates that a Cell occupies on a Worksheet.

Let say there are 20 members in our family. How can we call them, we can not call them Oldest guy, Older guy, .... , Younger guy and Youngest guy. We put some name to each of them, and identify accordingly.

In the same way, Excel Cells has its own names. That name is nothing but the Cell reference.

Cells Original Names : First Cell name A1 ........... last Cell name XFD1048576

Cells Pet Names : First Cell name R1C1 .......... last Cell name R1048576C16384

If you want to refer anything in any Excel Formula, you can refer the Cell instead entering the information.

Know more about Relative Cell Reference and Absolute Cell Reference.

4. Input Cell :-

The Cell in which each input value from a Data Table is substituted. Any cell on a worksheet can be the input cell. Although the input cell does not need to be part of the Data Table, the formulas in data tables must refer to the input cell.

In Excel, we have a concept What-if analysis. What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet.

There are three different what-if analysis tools in MS Excel. Those are Scenario Manager, Goal Seek and Data Tables.

In Data Tables, the Cells which are giving feeding to the final result are calling Input Cells. Any Cell in Excel can be the Input Cell for Data Table.

Data Table supports only two Input Cells, one should be row input cell and other should be column input cell.

5. Merged Cell :-

A single Cell that is created by combining two or more selected Cells.

In Excel, there is a facility to merge two or more Cells. You can able to merge only the Cell Range. Merging of Cells is for better presentation of data.

So, what is the name for merged cell ?

Let say, merged F10,F11,G10,G11 Cells as one Cell. The Cell reference for this merged Cell is the upper left Cell in the original original selected range.

What to share any ideas about these terms, please comment below.

You want more Excel tips, tricks, tutorials and formulas directly into your mail box. Subscribe here.

Convert to Debit/(Credit) Amount by using Paste Special

2012-09-06T16:30:00.000-07:00

Excel is one of the important tool for finance professionals. If I want to present anything or want to draft anything, my mouse directly hit Excel.

Today topic may be very much useful for the accounting guys, who post Journal Entries in the systems.

Most of the ERPs (Enterprise Resource Planning) allow the Journal Entry in the following way.

Particulars	Dr/(Cr) Amount
Cash a/c	$2,500.00
Income a/c	$(2,500.00)

But not in following way. However, sometimes we use this way to present the Journal Entry in the Excel.

Particulars	Dr Amount	Cr Amount
Cash a/c	$2,500.00
Income a/c		$2,500.00

As I said earlier, the presentation may be different from posting the entry into the system.

How to convert to Dr/(Cr) Amount from Dr Amount & Credit Amount

Or else, let me frame the question in this way that how to convert the second table data as first table data (referring above data).

I will take a real example and explain you further.

The entries in the Excel are as below.

Particulars	Dr Amount	Cr Amount
Cash a/c	$2,500.00
Income a/c		$25,000.00

Expense a/c	$4,900.00
Bank a/c		$4,900.00

Salaries a/c	$9,500.00
Telephone a/c	$800.00
Bank a/c		$10,300.00

Let say, the above data is in A1:C10 (including headers and blank cells)

Now, our aim is to present Debit and Credit Amounts in a simple column by putting positive for debit and negative for credit

1. Copy the Particulars column and paste in E column (E1:E10)
2. Type header in Cell F1 as Dr/(Cr) Amount
3. Copy B2:B10 and paste in F2
4. Copy C2:C10

5. Go to F2 - Right Click - Paste Special - Select Subtract from operations and check Skip blanks

6. That's All.

Then the final data in E1:F10 is as follows.

Particulars	Dr/(Cr) Amount
Cash a/c	$2,500.00
Income a/c	$(25,000.00)

Expense a/c	$4,900.00
Bank a/c	$(4,900.00)

Salaries a/c	$9,500.00
Telephone a/c	$800.00
Bank a/c	$(10,300.00)

Imagine if you have some 100's of Journal Entries in Excel. In that instances, we have to say 100 thanks to Paste Special options. Know more about Paste Special options from here

How do you solve this? Do have any alternative easier than this, please share to the world.

Get all these tips and tutorials directly into your mail box !!!

Subscribe to our newsletter

SUM Formula in Excel - (SUM Series)

2012-09-05T18:00:00.000-07:00

This article is part of SUM series. Know more about SUM series here.

In this SUM formula series, the first and important formula is SUM. All other SUM related formulas are derived from SUM formula only.

SUM is one of the basic function in MS Excel. May be basic, but more important in all the calculations.

What is SUM?

SUM is mathematical word. SUM means an amount or value obtained as a result of adding one or more numbers.

SUM formula in Excel?

Syntax: SUM(number1, [number2], [number3], ...)

If you see the syntax of the formula...

number1 is the number which we want to add.
number2 is the number which we want to add to the first number..
number3 is the number which we want to add to the first and second number...
and so on....

Do you observe that second argument (number2), third argument (number3) are in square brackets ([..]). The meaning is that these arguments are optional. In summary, first argument is compulsory and the other arguments are optional.

Argument can be a constant, Cell reference, Range, an array, a formula, result of another function or defined name
Max we can add, up to a total of 255 items.
Logical values and text are ignored in Cells, included if typed as arguments.

Example:

If you want to add 10,20,30,40

Option # 1

With out any defined formula, you can use symbols =10+20+30+40 and it results 100

Option # 2

You may use the formula =SUM(10,20,30,40)

This is equals to =10+20+30+40 and the SUM formula results 100.

Option # 3

you can enter these numbers in different Cells. Let say, 10 in A1 Cell, 20 in C5 Cell, 30 in E1 Cell and 40 in A10 Cell

Then the formula is =SUM(A1,C5,E1,A10)

This is equals to =SUM(10,20,30,40) This is because excel replaces the Cell reference with the data.

So, Finally you get the result 100.

Option # 4

You can enter these numbers in sequence cells. Let say, you have entered from Cell A1 through Cell A4

Then the formula is =SUM(A1:A4)

Excel reads the above formula as =SUM({A1,A2,A3,A4})

As we know from Option # 2 that, excel replaces the Cell reference with the data.

Finally the result is 100

What if I want to SUBTRACT something?

There is no SUBTRACT formula in Excel. In fact, subtract formula is not required.

Let say you want to subtract 30 from 60

Still you SUM formula holds good to use =SUM(-30,60)

And the result is 30, as expected

Know about AUTOSUM option in Excel?

AUTOSUM is one of the option which is useful to give the SUM function with single click. As name suggesting, it gives SUM function AUTOmatically once you click the AUTOSUM option from menu.

You can use the AUTOSUM option to quickly SUM a range of numbers in a column or row. Click an empty cell below a column of numbers or to the right of a row of numbers, and then click AUTOSUM option. Excel selects what it determines to be the most likely range of data. Then click enter if accept the range Excel selects, or you can select your own range before clicking enter.

For Example, you have some values in Cell G1, Cell G2, Cell G5, Cell G6. If you try to use Autosum option in Cell G7, then it gives the formula as =SUM(G5:G6). This is because Autosum takes most likely range. If the range is disconnects, Autosum may not apply for that. Then you have to select your own range.

This Autosum option perfectly works for Option # 4 above.

Shortcut for Autosum is Alt+=

Autosum for group of totals

Autosum option even works for one of more ranges at a time.

Data before Autosum

Now, I want to calculate the total of each sales person and total of each region.

Select the data including the blank cells (where you want the totals) then use Autosum option shortcut (ie., Alt+= )

Data after Autosum

So, with single shortcut Excel calculates 10 totals. That is what the power of Autosum option.

If you see independently, SUM formula is so basic. If you use SUM formula with the other formulas then you will enjoy the sweetness of SUM formula.

If I missed anything here, please let me know by giving your comment here.

SUM Formula Series - Introduction

2012-09-04T20:58:00.000-07:00

Most of the MS Excel users, start their excel formula learning with SUM function. Because SUM is one of the simple and easy function in MS Excel.

In simple terms, SUM means total value of two or more numbers. If we want to add two or more numbers in Excel, we can use SUM formula.

How many SUM related functions in Excel?

The answer is 8.

Correct, we all think that there is only one SUM function which calculates the total value. But there are other seven functions which also calculates the total value of two or more results.

I am starting the SUM Formula Series articles. In this series of articles, I will explain each SUM related formula on each day with one or more examples, starting from tomorrow.

Check this space daily for more information........

1. SUM Series - Introduction
2. SUM Series - SUM Formula in Excel
3. SUM Series - SUMPRODUCT Formula in Excel
4. SUM Series - SUMIF Formula in Excel
5. SUM Series - SUMIFS Formula in Excel
6. SUM Series - SUMSQ Formula in Excel
7. SUM Series - SUMX2MY2 Formula in Excel
8. SUM Series - SUMX2PY2 Formula in Excel
9. SUM Series - SUMXMY2 Formula in Excel

Do you know all the functions from the above list? What is your experience in using these functions. Let other know by comment below.

How to count total number of posts from archive in any Blog?

2012-09-03T19:44:00.000-07:00

If I visit any of the blog online, I used to see their archive to know the total number of posts. I do, bit analysis about the age of the blog, frequency of number of posts, number of feedburner subscribers etc.,

If number of posts are less, we can easily count the total. If number of posts are more and they enabled archive for weekly subtotal, it is very difficult to calculate the total number of posts for aged blogs.

Today, I will explain you how to count total number of posts from archive for any of the blog by using our MS Excel.

What is 'archive' in blog?

Blog 'archives' are history of your blog. These archives are acting as additional webpages, that contain all of your older posts. In simple terms, blog archives stores all of your posts so that they will always be there for others to read.

The following are the screenshots of archives in different blogs.

If you observe the first blog archive has the weekly frequency and the second has monthly frequency.

Steps to count total number of posts from archive in blog

1. Go to any of the blog (which should enable the archive feature on the home page)

Ex: www.lostinexcel.blogspot.com

2. Right click on the homepage

3. Click View page source (then webpage code window opens)

4. Find for "archive" by using Ctrl+F shortcut

5. If you find more than one "archive" you have to choose the correct "archive" word (where the list of months / weeks)

6. Copy the code just above from the first month/week code till last month / week code (see reference screenshot below)

7. Open an Excel and Paste the code over there. (either by using Paste special Values / Paste special Text) - Let say, you have pasted in column A from Cell A1

8. Enter the below formula in Cell B1

=MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)

9. Drag the formula from Cell B1 to the last Cell till data ends

10. Copy Column B - Paste as Paste special values in Column C - Convert all the items as numbers

11. Total it by using Sum function.

12. Thats All !!

Excel VAR Formula - Statistical Formula for 'Sample Variance'

2012-09-02T10:07:00.001-07:00

Today we all are going back to our school days, and learning some formulas in Statistics. Mean, Mode, Median, Variance, Standard Deviation etc., are words, which we use in the subject Statistics.

One of the Statistical concept is Variance. There is a formula in MS Excel for calculating Variance for a sample of data, that is VAR.

Before going into the excel formula VAR, let me explain what is Variance and what is the statistical formula for Variance.

What is Variance?

Statistical Variance gives a measure of how the data distributes itself about the mean or expected value. Unlike range that only looks at the extremes, the variance looks at all the data points and then determines their distribution.

In simple words, Variance is a measure of how far a set of numbers is spread out. In other words, Variance is describing how far the numbers lie from Mean (Average).

If we observe the above charts, from the mean point first chart data points are closer than second chart data points. So, we can say that Variance is more for the second chart data points.

Statistical Formula to calculate Variance?

The statistical formula for sample Variance is

Where as,
x=sample data
x̄=Mean of the sample data (average)
n=Number of sample data points\

∑=Sum

Solve the Variance for the sample data?

The below are the heights of male in India between the age 25 to 30 years (in cms).

Sample data picked up from population : 170,183,183,165,172,177,181 and 161

Mean = x̄ = Average(170,183,183,165,172,177,181,161) = (170+183+183+165+172+177+181+161)/8 = 174 cms

Height (cms)	Mean			Mean^2	Mean^2
x	x̄	(x-x̄)	(x-x̄)	(x-x̄)^2	(x-x̄)^2
170	174	=170-174	-4	=-4^2	16
183	174	=183-174	9	=9^2	81
183	174	=183-174	9	=9^2	81
165	174	=165-174	-9	=-9^2	81
172	174	=172-174	-2	=-2^2	4
177	174	=177-174	3	=3^2	9
181	174	=181-174	7	=7^2	49
161	174	=161-174	-13	=-13^2	169
				Total	490

From above table, ∑ (x- x̄)^2 = 490

Therefore Variance for the given sample is 490/(8-1) = 490/7 = 70 cms

Excel VAR formula?

Now, my work is very easy. If you understand the all above concept for Variance that is nothing but VAR formula.

To find out Variance, we did so many calculations. But MS Excel calculates Variance with a simple formula VAR.

Syntax of VAR formula: VAR(number1,number2,...)

Number1, number2, ... are 1 to 255 number arguments corresponding to a sample of a population.

Therefore, Variance for the above sample data is

=VAR(170,183,183,165,172,177,181,161) = 70 cms

Points to be remember for VAR formula?

(this part has been taken from MS Excel help)

Formula VAR assumes that its arguments are a sample of the population. If your data represents the entire population, then compute the variance by using Formula VARP.
Arguments can either be numbers or names, arrays, or references that contain numbers.
Logical values, and text representations of numbers that you type directly into the list of arguments are counted.
If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.
Arguments that are error values or text that cannot be translated into numbers cause errors.
If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the VARA function.

Finance professionals may not use this function regularly, but this function may be useful for the statistical professions and professionals who are working with surveys etc.,

Do you know the other side of 'Copy' in Excel? (Copy as Picture)

2012-08-31T11:17:00.000-07:00

Since we habituate to use Ctrl+C for Copy, we usually thought there is only one option for Copy.

In fact, Copy has two options. One is Copy as data (Ctrl+C) and other is Copy as Picture.

Try using Copy as picture option instead snipping tool to cut any Excel data and impress your boss in the meeting.

Without any late, I am starting to explain you the other side of Copy.

Types of Copy

Copy (as Data)
Copy as Picture

As shown on screen

As Picture format
As Bitmap image format

As shown when printed

1. Copy (as Data) :

Copy means copying something as it is.
Here something may be data or format or formula or column width or comment or even empty cell.
Once we pasted, either data or format or column width or comment appears/applies to the pasted cell.

Shortcut for Copy is Ctrl+C. You can use this option even from the menu bar.

2. Copy as Picture: This option to be used when you want to paste the copied data as Picture/Image.

i) As shown on screen: Here the intention is Excel displays as picture on the screen and that picture may not appear while printing.

a) As Picture Format :

This Option is part of Picture as shown on screen.
Once you paste, then excel displays transparent image. Means you can able to see the data behind this picture.
If you observe from the below pic, grid lines of the Cells also captured along with the picture.

b) As Bitmap image format :

This is the second option under the Picture as shown on screen, Once you paste, then excel displays like normal image/picture.
This is not a transparent picture.

ii) As shown when printed:

In this Copy Option, Excel allows the pasted image to print.
Once you pasted the copy data, excel displays transparent image but without grid lines.
Excel allows this picture also, when printed.

Did you ever used the second option? And in which situation you have used the second option? Let others know by commenting below.

20 Useful Excel Articles Links @ One Place

2012-08-29T03:00:00.000-07:00

Here is the list to 20 Useful Excel Tutorials, Tips and Articles.

Which post you liked more? Comment below.

How To Make Interactive Images In Ms Excel ?

2012-08-28T08:20:00.003-07:00

It is possible to make an Interactive Images with MS Excel. This article gives you the detailed explanation about the Interactive Images and step by step instructions to make an Interactive Images in Excel.

What is Interactive Images in Excel

"Change of image by changing data in a particular Cell."

For Example, If I type Google in a Cell then google logo should appear. If I change the data from Google to Facebook, then the image also should change from Google Logo to Facebook Logo.

Options to be used

Camera Option
Data Validation
Define Name

Formulas to be used

Vlookup
Indirect
Concatenate (&)

How to add the Camera option to the Quick Access Toolbar

The below is calling as Quick Access Toolbar in MS Excel.

If we want to keep any option handy, then we can enable that option to the Quick Access Toolbar. So that instead of searching for the option from Main Menu, we can directly use from Quick Access Toolbar with single click.

Follow the below steps to add the Camera option to the Quick Access Toolbar

Go to File from Menu bar (for 2010 excel) or Go to Office Button (for 2007 excel)
Click Options (then Excel Options window opens)
Select Quick Access Toolbar from the left side bar
Choose All Commands from the drop down for Choose commands from:
Select Camera option from the list under All Commands
Click Add to add the Camera option to the Quick Access Toolbar
Click OK

Now, you can able to find the Camera option in the Quick Access Toolbar

Export the required Images to Excel

First of all, save all the images from web or from other source to your computer. So that we can insert these images in excel.

Follow the below steps to insert images into excel

Click Picture from the Illustrations group under Insert menu
Select the picture from the Computer
Follow steps 1 to 2 to export all the pictures from Computer to Excel (let say, you have exported 4 pictures of Google, Facebook, MSN and Twitter Logos)
Re size the width and height of all the pictures by dragging the edge of the picture
Move the 1^st Picture to the Cell A1, second picture to Cell A2, third picture to Cell A3 and last picture to Cell A4
Ensure that one pic should be fit in one cell, by adjusting the column width and row height of the respective Cells
Rename the sheet as Image List

Steps to make the Interactive Images

Go to the new worksheet (not workbook) and name it as “Interactive Image”
Go to A1 Cell
Click Data Validation from Data Tools group under Data menu
Select List from the Allow drop down in Settings Category
Type Google,Facebook,MSN,Twitter in Source box
Click OK
Enter the below data from G1 through H4 Cells

Use shortcut Ctrl+F3 (then name manager box appears)
Click New (then New Name window appears)
Type Name as "Image"
Enter the below formula in the Refers to box

=INDIRECT("Image!A"&VLOOKUP('Interactive Image'!$A$1,'Interactive Image'!$G$1:$H$4,2,0))

Click OK (then you returns to Name Manager window)
Click Close
Go to Cell G1
Click Camera option from the Quick Access Toolbar (then you get a cross symbol as cursor)
Drop that cross symbol somewhere in the visible Cell (then excel displays image showing G1 Cell content)
Change the formula to =image from =$G$1 (once you select the image you can find the formula in the formula bar)
Drag and drop the pic where ever it has to display

Now, you can change the data in A1 Cell then you can find changing the pic.

What is there in your mind? Have you tried the above steps. Share your experience that how it is working. Comment below.

Create Interactive Data Validation by Using Indirect Formula

2012-05-24T11:07:00.001-07:00

Data Validation is one of the good option in MS Excel.

Now, we will see how to Create Interactive Data Validation in MS Excel.

Options to be used :

Data Validation
Define Name

Formulas to be used :

INDIRECT

Steps to be followed: -

We are trying to apply the Data Validation for the below.

Which Social Networking Site you like more?

Two answers needs to be filled for the above question. The second option is based on the answer selected as one.

Follow the below steps to Create Interactive Data Validation.

1. Enter Data

Enter the below data from Cell A1 to C3

Secret Question	Site Name	Option
Which Social Networking Site You Like More?

A2 Cell has the question and the answer should be in B2 and C2.

We are allowing user to select only one of the Site Name from the Social Networking sites Facebook, Twitter, Orkut, Google+.

Enter the below data starting from Cell E2 to H4.

Facebook	Twitter	Orkut	Google_Plus
Like	Follow	Scraps	Hangouts
Share	Tweet	Communities	Circles
Tag		Recent visitors	+1 Button

Above, we have entered the master data having Site Names and Options under those sites.

2. Define Names

Defining Name for the Site Name List

Select E2:H2
Use short cut Ctrl + F3 (then Name Manager box appears)
Click New (Alt + N)
Enter Name as Social_Site
Click OK (then defined name appears in the Name Manager box)
Click Close

Defining Name for the Site Options

If we want to Define Name for the options under all sites, we have to do the above steps four times.

Instead, we had an option to Define Names for all the four sites at a time.

Select E2:H4
Ctrl + Shift + F3 (then Create Names from Selection box appears)
Check Top row option (Alt + T) (make sure all other options are unchecked)
Click OK

Excel has been defined three names. Those are Facebook, Twitter, Orkut and Google_Plus

3. Apply Data Validation

We are using Data Validation option in Excel in Cell B2 and C2.

Select B2 to validate
On the Data tab, in the Data Tools group, click Data Validation (then Data Validation box appears)
Select Settings Tab
Select List from the Drop down list under Allow (Alt + A)
Enter =Social_Site in Source
Click Ok

You can find the drop down in the Cell B2 having the names Facebook, Twitter, Orkut and Google_Plus. User should select one of the Site Name from the drop down list.

Now, we will apply Interactive Data Validation in C2

Interactive Data Validation is nothing but data will be changing, once you change the dependent cells data.

In this example, you should be having the options only Follow, Tweet if you select Twitter as Site Name. If you select Facebook, the options list should be changed to Like, Share and Tag.

Select C2 to validate
On the Data tab, in the Data Tools group, click Data Validation (then Data Validation box appears)
Select Settings Tab
Select List from the Drop down list under Allow
Enter =Indirect (B2) in Source
Click OK (then should be getting a Microsoft Office Excel error box)
Click Yes

Now, see what is happening in C2. If you select Facebook in B2 then the Facebook options appearing in C2 and if you select Twitter then the Twitter options appearing in C2.

Time Saving Short Cuts in Excel (Ctrl + Enter) & (Shift + -)

2012-05-23T09:31:00.003-07:00

How to select multiple cells (without holding Ctrl) ?

1. Go to the first cell in the desired selection

2. Shift + -

3. Simply select other desired cells

How to enter the same data into the multiple cells ?

1. Select all the cells where you want to fill the same data

2. Enter the data

3. Ctrl + Enter

Note: You can use the above short cut not only to enter the data but also edit the formula in multiple cells.

How To Match Text Formatted Number ('010) With Absolute Number (10) In MS Excel ? (Vlookup Formula Trick)

2012-04-26T11:00:00.001-07:00

Real time scenario: Mr. John has downloaded an Excel data from his internal ERP system. In one of the column the numbers are displaying as text ('010,'020,'030..). He want to lookup the those numbers from the master list by using Vlookup Formula. But the master list has the absolute numbers (10,20,30..). When he is trying to put the Vlookup Formula and Excel is resulting the error #N/A. This is because Excel can't match text formatted number ('010) with the absolute number (10).

What you do to use Vlookup Formula?

Most of the members do the following :

"They convert the text numbers ('010,'020,'030..) into the numbers". Then excel simply match the absolute number with the exact absolute number. PROBLEM SOLVED !!!

No. The above is not the proper solution. Why because if we has one more master file which has text formatted numbers then how could you match this converted absolute number with the text formatted numbers in the master file.

The Proper Solution

Use the below vlookup formula

=Vlookup (text_format_number + 0 , table_array , column_index_number , false) or,

=Vlookup (text_format_number * 1 , table_array , column_index_number, false)

Now, we can say that the problem solved.

How it works ?

Excel can not match the text formatted number ('010) with the absolute number (10).

But excel can do calculation with any of the text format number.

For example, if we add 2 to absolute number 10 then excel results 12 (2 +10 = 12). If we add 2 to text formatted number '010 then excel results the same (2 + '010 = 12). The important point here is the excel displays the result as absolute number.

Do you have any alternative solutions please let users know by giving comment below.

All Worksheet Shortcuts @ One Place

2012-02-20T05:12:00.002-08:00

I am trying to place all the Excel Worksheet related shortcuts here.

Please find the following Worksheet related shortcut keys

01. Add New Sheet : Shift + F11 (or) Alt + Shift + F1 (or) Alt + I, W

02. Delete Worksheet : Alt + E, L

03. Change Worksheet Color : Alt + O, H, T

04. Rename Worksheet : Alt + O, H, R
05. Hide Worksheet : Alt + O, H, H
06. Unhide Worksheet : Alt + O, H, U, Down Arrow/Up Arrow (to select hided sheet), Enter

07. Move to the next Worksheet : Ctrl + Page Down

08. Move to the previous Worksheet : Ctrl + Page Up

09. Move to the Next Worksheet Pane : F6

10. Move to the Previous Worksheet Pane : Shift + F6

11. Copy the Worksheet : Alt + E, M, Alt + C, Enter

12. Move the Worksheet in the same Workbook : Alt + E, M, Up Arrow/Down Arrow, Enter

13. Move the Worksheet to the other Workbook or new Workbook : Alt + E, M, Alt + T, Up Arrow/Down Arrow, Enter

14. Select current and next Worksheet : Shift + Ctrl + Page Down

15. Select current and previous Worksheet : Shift + Ctrl + Page Up

Hope, I have covered all the Worksheet related short cut keys above. If you know any other shortcut relates to Worksheet please let us know.

Which Shortcut you use frequently in your office ? Please let us know by giving comment below.

Relative Reference Vs Absolute Reference In MS Excel

2012-02-18T02:50:00.000-08:00

Use Dollar Symbol..
Use Dollar Symbol..
You may not know about Relative Reference and Absolute Reference in Excel but you might heard that "using dollar ($) symbol in excel..". The name of using dollar ($) symbol in reference is nothing but Relative Reference and Absolute Reference.

What is Cell Reference ?

A Cell Reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Excel can find the values or data that you want that formula to calculate.

In one or several formulas, we can use a cell reference to refer to :

Data from one cell on the other worksheet
Data that is contained in different areas of a worksheet
Data inc cells on other worksheets in the same workbook

For Example : =A1 refers to Cell A1 and returns the value in Cell A1

=Reference!A1 refers to Cell A1 on Reference worksheet and returns the value in Cell A1 on Reference worksheet.

There are four types of Cell References in Excel

Relative Row and Relative Column Reference
Relative Row and Absolute Column Reference
Absolute Row and Relative Column Reference
Absolute Row and Absolute Column Reference

1. Relative Row and Relative Column Reference (=A1)

Neither the Column nor the Row are fixed. Both the Column and Row are relatively changes when changing the reference from one cell to other cell.

I have given the reference A2 in the Cell B2 and it results the value in A2 ie., John.
Once we do auto fill till A5 then the reference also changes relatively. The reference A3 appears in B3, A4 appears in B4 and A5 appears in B5 Cell and the respective cell reference values display as results.

2. Relative Row and Absolute Column Reference (=$A1)

Only Column is fixed and Row is relatively changes when changing reference from one cell to other cell.

Relative means in a formula, the address of a cell based on the relative position of the cell that contains the formula and cell referred to. When you copy the formula, the reference automatically adjusts.

Absolute means in a formula, the exact address of cell, regardless of the position of the cell that contains the formula.

To make column as fixed we need to use $ (Dollar) symbol before the Column Reference.

In this example, I have entered the reference $B1 in B2 cell and the result should be the value of B1 Cell. Since I have fixed Column B by using $ symbol, even if I used auto fill either across the columns or rows, I may not find any change in column but row changes relatively.
3. Absolute Row and Relative Column Reference (=A$1)

Only Row is fixed and Column is relatively changes when changing reference from one cell to other cell.

Absolute means in a formula, the exact address of cell, regardless of the position of the cell that contains the formula.

Relative means in a formula, the address of a cell based on the relative position of the cell that contains the formula and cell referred to. When you copy the formula, the reference automatically adjusts.

To make Row as fixed we need to use $ (Dollar) symbol before the Row reference.

In this example, I have entered the reference B$1 in B2 cell and the result should be the value of B1 Cell. Since I have fixed Row 1 by using $ symbol, even if I used auto fill either across the columns or rows, I may not find any change in Row but Column changes relatively.

4. Absolute Row and Absolute Column Reference (=$A$1)

Both the Column and Row are fixed. Neither will be changed nor incremented during using auto-fill option.

Absolute means in a formula, the exact address of cell, regardless of the position of the cell that contains the formula.

I have given the reference A2 in the Cell B2 and it results the value in A2 ie., Saran. Since I have used $ symbol before both column and row, even I use auto-fill either across the column or across the row, excel takes only $A$2 as cell reference. It gives the value of A2 at any time.

Shortcut to switch between Cell References?

Once enter cell reference in any of the formula, you can use Function Key F4 to switch between one cell reference to the other.

Share your thoughts and comments on this post by giving comment below.

Get Excel Tricks and Tips through mail Click Here

How To Create Chart By Using REPT Formula In MS Excel (Excel Trick)

2012-02-11T09:58:00.000-08:00

As everybody knows, excel has very good chart options to create excellent charts. But here we are not at all touching any of the Excel Chart option. I can create a simple bar chart by using REPT formula. This is one of the trick you can use while creating dashboards.

Since this chart we creating by using REPT formula, let me start to explain REPT formula as first part.

All About REPT Formula?

Syntex of the REPT formula

=REPT(text,number_times)

Here,
Text is the text you want to repeat
Number_times is a positive number specifying the number of times to repeat text

In simple terms, REPT repeats text a given number of times. We use REPT to fill a cell with a number of instances of a text string.

For Example, If we want to repeat alphabet 'S' as 10 times in a cell, then we have to use REPT formula as =REPT("S",10)

Then the result will be SSSSSSSSSS

How to create Chart with REPT function?

The below is the data which I am going to use to create a chart.

Name	Marks
Saran	75
Harsha	21
Vishnu	64
Maruthi	42
Bhyrav	90
Raja	12

Then, use the below formula in C2.

=REPT("I",B2)

Copy and Paste the same formula till the data ends ie., C7 cell.

Name	Marks
Saran	75	=REPT("I",F6)
Harsha	21	=REPT("I",F7)
Vishnu	64	=REPT("I",F8)
Maruthi	42	=REPT("I",F9)
Bhyrav	90	=REPT("I",F10)
Raja	12	=REPT("I",F11)

Then, it results as below.

Name	Marks
Saran	75	IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
Harsha	21	IIIIIIIIIIIIIIIIIIIII
Vishnu	64	IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
Maruthi	42	IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
Bhyrav	90	IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
Raja	12	IIIIIIIIIIII

The above result is nothing but the simple chart. Isn't it?

In fact, the above chart bars are horizontal. If you want to create chart with vertical bars.. then, use Format option in Excel.

Formula To Convert Date To Financial Year In MS Excel (07-Feb-1987 = FY 1986-87)

2012-02-04T01:18:00.000-08:00

Today one of my friend asked me, the Formula to converts Date into Financial Year in MS Excel. I thought it is better to explain the same in my blog for the interest of other visitors.

Financial Year is 12 Months period which usually the Company follows according to their Accounting Policy. Here, I am going to explain you the Formula for the Financial Year which starts from the 1st Day of April Month and ends on 31st Day of March Month in the Following Year.

For Example, the Financial Year of 04 February, 2012 is 2011-12

Formula :

=IF(MONTH(A1)>3,"FY "&YEAR(A1)&"-"&RIGHT(YEAR(A1)+1,2),"FY "&YEAR(A1)-1&"-"&RIGHT(YEAR(A1),2))

Functions used in the Formula?

The formula consists of the following Excel Functions

Logical Function - IF
Date Functions - Month & Year
Text Function - Right

How the Formula Works?

In Simple Text..

If the Month number of the Date is Greater Than 3 (three).. then the result should be the Year of the Date and Following Year of Date or else, the result should be the Prior Year of the Date and Year of the Date. Of course, Hyphen symbol (-) between these two years.

RIGHT Function in the Formula is just to format the result. If we want the result as FY 2010-2011 then RIGHT function is not required. But we want to show the result as FY 2010-11 then we have to use Right Function (How to use Right Function in MS Excel). Right Function will results the last two characters of the second part of the result.

& symbol in the Formula is to combine all parts of the result. Even you can use CONCATENATE Function for the same result.