MS Excel and MS Word Tips






Useful excel functions

PIVOT TABLES

VLOOKUP

KEYBOARD SHORT CUTS

SUMIF

paste special

Trace Precedents or Trace Dependents  Auditing Features

KEY BOARD SHORTCUTS



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
For Fill Colour - Alt + H + H






Format the Numbers as you like them

To format the numbers through the 'Format Cells' dailog box ,the short cut key is
CTRL+1
Some of the important formatting of numbers without going to  'Format Cells' dailog box
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.
CTRL+!
Converts to Currency format                         
with two decimal places (negative numbers in parentheses).
CTRL+$
Dont want any formatting to a number

CTRL+~



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.

Data Entry Tips 

Fill One Cell by copying from other cells

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

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.....


Functions


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


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 "

For Selecting Constant Numbers Only

Go To ( Cntrl + G )

Click on "Special"

Select "Constant "



Print

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 TABLE

PIVOT CHART


GROUP/UNGROUP PARTS OF SPREADSHEETS

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



SUBTOTAL
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.

=SUM(Sheet1!G7:G9)

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

=SUM(Sheet1:Sheet2!G7)

or range of cells.

=SUM(Sheet1:Sheet2!G7:G9)

You can download the example for this at this link 
https://www.dropbox.com/s/knr2uektxze6yjs/data_consolidation_in_excel.pdf?dl=0



MS WORD  

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 LettertOGGLE 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)


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

MS OUTLOOK
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

BROWSER -CHROME
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 -GENERAL
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









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




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 formatting 
Copy formatting quickly with the F4 key The F4 key is a useful shortcut in Excel that enables you to copy any formatting you have applied to a cell to another cell. To copy formatting from one cell to another cell (or column), just apply the formatting, click where you want the formatting to appear and press F4

Format Paint Multiple Cells

Double click format button

Insert Table

Alt NT or Ctrl T to convert into table

Date


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

Select Multiple Cells

Press and hold Cntrl Button  or much flexible is Shift F8

Format Cells - Custom


IRR Vs XIRR



SUMIF vs SUMIFS

Syntax is reverse btw both of them

SUM VS SUBTOTAL

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



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.


AGGREGATE




INSERT SYMBOL ALT IS -Wingdings