MS Excel and MS Word Tips for Accountants


For Fill Colour - Alt + H + H
For Merge : Alt HMC : Merge and centre ( for Heading)
For Merge across : Alt HMA : Merge across

For simple outline : HBS
For boarder all cells: HBA

Another way for autofit - VBA

Format the Numbers as you like them

To format the numbers through the 'Format Cells' dailog box ,the short cut key is
Some of the important formatting of numbers without going to  'Format Cells' dailog box 
Ctrl 1 effect - prominent among them -obtained through Ctrl Shift 1,2,3,4
Converts to Date format with the day, month, and year.CTRL+# ( # is shift+3 So this is Shift+Cntrl+3
Converts to Number format                                  
with two decimal places, thousands separator, and minus sign (-) for negative values.
Cntl Shift 1
Converts to Currency format                         
with two decimal places (negative numbers in parentheses).
Cnt Shft 4
Dont want any formatting to a number

Cnt Shift `

Data Filter in excel 
Cntl+A                  select All Data
Alt A T                 Apply Filters
Alt Down Arrow  Open the filter
Space Bar             To select, deselect lines
Enter                     Close the opened filter
Alt A T                 To remove the filter and go back to full data

Apply desired editing on filtered data

Go To Special          Cntr G
Visible cells only     Alt  S   then Y then enter
Then do the editing

Fn F4 in excel

When you want to repeat the last action that you've just done, just select the range of cells where you want to repeat it an press F4.For example, if you have just deleted a row and you want to delete another row, simply select the range where you want to delete the next row and press F4. If you have just added cell borders to a range and you format another range the same way, F4 will repeat the previous action for you. This is often most useful when you want to repeat an action over and over.
F4 when a formula is selected
If you select a formula containing a range and press F4 you get dollar symbol and that locks the range.

Advanced Filter

Data Entry Tips 

Fill One Cell by copying from other cells

• Ctrl-R  to fill right
. Ctrl-D  to fill down
   Fill Series

Fill many cells by copying from other cells
• Select the cell with the content to be copied and drag to select the cells to which the content should be copied

Data selection

How to move the first or last cell of a contiguous data block without scrolling

•Ctrl-Arrow   :  Move to the first/last data cell in the arrow direction
•Ctrl-Shift-Arrow  :  Selects the cells between the current cell and the first/last data cell

Format for Your Data

• You are entering 00125678 and system is taking as 125678 or as a date.
• The format of the cells that you are entering data in being presumed by excel as something else. You need to tell excel not to presume any particular format.

• (Go to Format and click on Cells. Go to Number tab) or use Keyboard Short Cut  Cntrl + 1 . In the Category list, choose Text and click ok.Similarly changing of number format to date format etc.....

Selecting few cells only for applying required functioning on the same) :  

Go to - Special - Visible Cells only 

Go To ( Cntrl + G ) ,Click on "Special", Select "Visible Cells Only "

Shortcut for the above is Alt Semicolon  ie. Alt ;

For Selecting Constant Numbers Only

Go To ( Cntrl + G )

Click on "Special"

Select "Constant "

Find & Replace

Ctr1 F Find Ctrl H Replace
Find ALL Select All : In case of change in formatting - Instead of replace from Ctrl H - you can say Find All and Ctrl A ( Select ALL) and then format them



AMKL    PP   3456G
SUBSTITUTE(G12," ","")
Substitues  spaces (" ") with no space ("")  Result :All mid space is removed


Before you Print Excel Sheet next time ensire to follow this to save time and papers.
Manytimes we print first and then find the print is not as expected and then print again after necessary changes.

·         Step 1 : Set print area - Alt PRS / Clear Print Area – Alt PRC

·         Step 2 : See page preview Ctrl P

·         Step 3  : Change page setup if required Alt PSP

Within Page Setup Dialogue box, there are four Tabs

·         Page

·         Margin

·         Header/ Footer

·         Sheet – With in sheet “Print Titles” - Instead of giving a header/footer in the above tab, we can select some of the rows within the sheet as “Titles”




Pivot:Group By



Mark the row or column that you would like to “fold”, i.e. hide for the moment.

Click on Data: Group and Outline: Group

To “fold” click now on the “minus” sign outside of your column or row

You may also group or ungroup hierarchically

Instead of ‘=sum(range)’ add ‘=subtotal(9,range)’ where you need a subtotal or total.

Formula across the sheets for consolidation of data

We routinely use formula's with in a sheet across various cells.


But we can also use across the sheets for a given cell


or range of cells.


You can download the example for this at this link


Excel Functions for Finance

FV formula

PMT formula for finding EMI

RATE function for finding IRR in a EMI based cashflows

IRR and XIRR have to be used instead of RATE if we are working with irregular cashflows


Shift F3 for Changing CAPITAL LETTERS to Sentence Case
Changing CAPITAL LETTERS to Sentence Case
Microsoft Word has a handy function to change case, and if you wanted to change it all - this is what you do:

1. Select all the text (Ctrl +A)
2. Select the 
Change Case button from the ribbon at the top
3. Select the option you require

Sentence case = makes capital letters for the beginning of sentences only and the rest lower caselower case = makes all text lower caseUPPER CASE = makes all the text CAPITAL LETTERSCapitalize Each Word = makes the First Letter Of Each Word A Capital Letter tOGGLE cASE = swaps your CAPS to lower case and lower case to CAPS

Try the keyboard shortcut - select the text you wish to modify then whilst holding down the Shift key tap the F3 key (tap it again to cycle through CAPITAL, lower case and Sentence case)


Ctrl +J : Justify paragraph alignment
Ctrl +Shift+> : Increase font size
Ctrl + 5          : 1.5 spacing in the paragraph

How to search for email from nitin to cfo in which you are in cc and mail contained the word talentwave invoice and had attachments

 Answer is shift+ctrl+F called as advanced search.. In that all these options can be selected

In browser Google Chrome – Cntrl H is for browsed  history. In that there is a search bar.If we have done similar search earlier and want that link again -we can search and get those links easily there.

If we have recently opened a tab and closed, even without going to Cntrl H and picking from there we can directly use Shift Ctrl T

Ctrl-Shift-D for bookmarking any webpage for future reference

Windows Button Plus E is explorer view of your folders
Use Select and Enter to navigate into the folders
Use Back Space to navigate out of folders
How to PIN to Start Button 

Select Row with Shift Space and Column with Ctrl Space (C with C space )

Other Excel Tips

Name your Formulas

Name your formulas with named ranges If you are using complicated formulas, you can give them a name to help you and your team understand and remember what they are. For example, you can name your first quarter sales formula ‘FirstQuarterSales’, or your income tax formula ‘IncomeTax’. This is very useful for spreadsheets that multiple people are working on. 1. To do this, go to the Formulas tab. 2. Click Define Name and select which cells it refers to. By naming all of your different formulas in this way, Excel keeps a list of them under the Name Managerbutton, allowing you to edit them and apply them to different cells quickly and easily.

Copy to multiple places 
Do once an press f4 for other cells

Copy once and paste Format Paint Multiple Cells

Double click format button

Select Multiple Cells

Press and hold Cntrl Button  or much flexible is Shift F8

Format Cells - Custom
 you can define the format you need

Insert Table

Alt NT or Ctrl T to convert into table


=NOW() will give todays date and whenever file is opened it will show current date




Syntax is reverse btw both of them


FEATURES OF SUBTOTAL will calculate on visible cells only when the range is under filter

Subtotal is defualt option in "Table"

Insert Table - Alt N T or Ctrl T

Once data range is converted to table, filter comes along with it and it is ready for sorting and formatting. The desired table design can be set as default.

Also for such table when sum is made,it uses SUBTOTAL instead of SUM.



There is a new function in excel introduced on 28th August 2019 *XLOOKUP*

*XLOOKUP* is the successor to the *VLOOKUP and HLOOKUP*

_*Why release a new lookup function?*_
While VLOOKUP was widely used, it has several well-known limitations which XLOOKUP overcomes: 

*1. Defaults to an "Approximate" match:* 

Most often users want an Exact match, but this is not VLOOKUP's default behavior. To perform an Exact match, you need to set the 4th argument to FALSE. If you forget (which is easy to do), you'll probably get the wrong answer.

*2. Does not support column insertions/deletions:*

VLOOKUP's 3rd argument is the column number you'd like returned. Because this is a number, if you insert or delete a column you need to increment or decrement the column number inside the VLOOKUP.

*3. Cannot look to the left:*

VLOOKUP always searches the 1st column, then returns a column to the right. There is no way to return values from a column to the left, forcing users to rearrange their data.

*4. Cannot search from the back:*

If you want to find the last occurrence, you need to reverse the order of your data. 

*5. Cannot search for next larger item:*

When performing an "approximate" match, only the next smaller item can be returned and only if correctly sorted.

*6. References more cells than necessary:*

VLOOKUP 2nd argument, table_array, needs to stretch from the lookup column to the results column. As a result, it typically references more cells than it truly depends on. This could result in unnecessary calculations, reducing the performance of your spreadsheets.

*What's the future for VLOOKUP & HLOOKUP?*

VLOOKUP and HLOOKUP will both continue to be supported by Excel. That said, we strongly recommend using XLOOKUP in favor of VLOOKUP and HLOOKUP because XLOOKUP is simpler to use and less prone to error.

*Availability Notes*

XLOOKUP and XMATCH are available for users signed up for the Office 365 Insiders Program starting today and will continue rolling out to Insiders over the next few weeks. Gradual roll outs allow us to gather feedback and ensure feature quality.



Summary of Useful excel functions





paste special

Trace Precedents or Trace Dependents  Auditing Features


For Page setup - Alt + P + R + S
For Print Preview - Ctrl + F2
For Pivot Table  - Alt + N + V + T
For Pasting Links - Alt + E + S + L