Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

Which Excel formulas make your day easier?

user-image
Question added by Dua'a Hamed .M AlQwasmi , Oracle forms and Java EE Developer , الجامعه الأردنية - The University Of Jordan
Date Posted: 2013/11/01
Zafar Iqbal
by Zafar Iqbal , Teacher (Pak Studies) Subject Specialist , Home Tutor

=vlookup()

Mazher M Khan
by Mazher M Khan , Chief Accountant , Saudi Investment Company

I think VLOOKUP formula makes your day easier. however it depends what kind of data you working for or what do u want.

Muhammad Zeeshan Sarwar
by Muhammad Zeeshan Sarwar , Financial Controller , Arveen General Trading LLC

Following are the "favorite" & straightforward functions which make my life easy while doing work on Excel:

 

Ø    Formatting As Fast As1 –2 –3 –4 –5 –6 –7 –8!

Ctrl + Shift + 1            Number format with2 decimal places                                   

Ctrl + Shift + 2            Format as time                                                                      

Ctrl + Shift + 3            Format as Date                                                                      

Ctrl + Shift + 4            Format as currency value

Ctrl + Shift + 5            Format as percentage           

Ctrl + Shift + 6            Format number in exponential form or scientific form

Ctrl + Shift + 7            Apply single line border to selection

Ctrl + Shift + 8            Make a selection of contiguous cells

 

Ø    Navigate Through Different Worksheets And Workbooks:

Ctrl + PageUp              To switch to next worksheets                                    

Ctrl + PageDown         To switch to previous worksheets                              

Ctrl + Tab                    To navigate through different workbooks     

 

Ø    Alt + =             To Apply Autosum    

Ø    Ctrl + ;             Shortcut To Enter Todays Date

           

Ø    Select Entire Table at Once:    Ctrl + Shift + Space

(You must select one of the cells within the range (within table) to correctly select the relevant table.)

 

Ø    Detect All Cells Linked To a Formula:

What if you are new to a certain data and don’t know what is linked to what. Situation gets more complicated when one cell contains a formula that depends on data in many other cells. Things would become so easy to understand if you are able to know each and every cell on which a cell is directly or indirectly dependent. Well next short cut is just for this job!

 Ctrl + Shift + {

 

 Ø    Toggle Between Cell Value And Formula:

Usually excel users have to double click the cell to view the formula and that only reveals formula in just one cell. Following shortcut reveals the formulas across the whole excel worksheet.

 Ctrl + ~

 

Ø    Extended Selection:

For a clean, smooth selection of cells but without using mouse:

F8

How to use it:

Press F8 once and use direction keys to move the selection in the appropriate direction to select desired cells. You don’t have to hold down “Shift” key to do the selection. Once F8 is pressed and ‘Extend Selection” mode is active you can either use direction keys to make the selection or mouse to make or extend the selection.

Once F8 is pressed a notification of ‘Extend Selection’ can be seen in the bottom status bar of excel to let user know that extended selection mode is enabled. Pressing F8 again will disable ‘Extend Selection’ mode.

 

Ø    Toggle Cell Reference / Repeat Last Action

Two uses are explained below at full length separately

F4

i) Toggle absolute and relative reference

With formula in edit mode, move cursor to any cell address and press F4 key. You will notice dollar sign ‘$’ appearing before row number and column letter. Pressing this will fix/define the cell reference in formula i.e. reference shall not be changed while dragging the formula in any direction.

ii) Repeat last command or action

If you are doing something on repetitive basis let it be a formula, pasting, formatting, underlining etc then instead of pushing key combination over and over again you can do it just by pressing F4 while in non-editing mode. For example if you have copied a certain value and want to paste it in several cells then you don’t have to click paste button with mouse or Ctrl + V again and again. Just press F4 and it will be pasted. F4 repeats the last action.

Balaji Kobula Premanth
by Balaji Kobula Premanth , Senior Accountant , Ishtar Decor LLC

vlookup()

alt+(+) ie sum 

etc....

 

mohammed ganbah
by mohammed ganbah , Pharmacies department manager , teb al'osrah medical company

doing alot of quations per sheet

i think sumifs is the most one i use

and it gives me great results

lol

Rangarajan Ramaswamy, PMP, PMI-SP, RMP, CCP
by Rangarajan Ramaswamy, PMP, PMI-SP, RMP, CCP , Planning Engineer , Six Construct Ltd.

Hi, considering you know the very basics of excel, the following excel formuals can make your day much much easier :-

 

1. Vlookup

2. Hlookup

3. Sumif

4. Sumifs

4. Count

5. Counta

6. Countif

7. Countifs

8. if

9. round

10. lower

11. upper

12. concatenate

 

There are many more, but i believe this solves most of our work. Take care.

Ali Mahdi Jawad
by Ali Mahdi Jawad , Head of Information Technology , Falcon Cement Co

Vlookup

Hlookup

Left

Right

Countif

khader zarafili
by khader zarafili , Site Engineer (Mechanical) , Arabtic Consolidated Contractors Limited

if + sum

sumif

Azharuddin Mohammed Khaja
by Azharuddin Mohammed Khaja , FINANCE & ADMINISTRATION EXECUTIVE , AIN CORPORATION SECURITY SOLUTIONS INDIA PVT LTD

It entirely depends on the company and most important the field for which you work for.

Muhammad Faheem
by Muhammad Faheem , Consultant- Accounts, Audit & Taxation , Basim Associates

Vlookup that certainly helps me in arranging and analyzing the data frequently.

More Questions Like This