HOME TAB OF RIBBON (Alt H)
FUNCTIONS AND SHORTCUTS AVAILABLE
For Fill Colour - Alt + H + H
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 | CTRL+1 |
| 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. | CTRL+! Cntl Shift 1 |
| Converts to Currency format with two decimal places (negative numbers in parentheses). | CTRL+$ Cnt Shft 4 |
| Dont want any formatting to a number | |
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
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
https://youtu.be/ZZiS64_auX4
Data Entry Tips
Fill many cells by copying from other cells
• (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 ;
FUNCTION KEYS
Formulas
Print
PIVOT CHART
DISTINCT COUNT
Fill One Cell by copying from other cells
• Ctrl-R to fill right
. Ctrl-D to fill down
Fill Series
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
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
Go To ( Cntrl + G )
Click on "Special"
Select "Constant "
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
FUNCTION KEYS
Formula
|
Issue
|
Syntax
|
Analysis
|
Substitute
|
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”
INSERT TAB OF RIBBON (Alt N)
FUNCTIONS AND SHORTCUTS AVAILABLE
PIVOT TABLE
PIVOT CHART
Pivot:Group By
DISTINCT COUNT
GROUP/UNGROUP PARTS OF SPREADSHEETS
•Mark the row or column that you would like to “fold”, i.e. hide for the moment.
•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
GOAL SEEK
Shift F3 for Changing CAPITAL LETTERS to Sentence Case
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
COPY THE FORMATTING
Paragraph
Ctrl +J : Justify paragraph alignment
MS OUTLOOK
WINDOWS -GENERAL
GOAL SEEK
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 WORD
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)
COPY THE FORMATTING
Paragraph
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
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
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
Select Row with Shift Space and Column with Ctrl Space (C with C space )
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.
Insert Table
Alt NT or Ctrl T to convert into table
Date
CAGR(RRI)
IRR Vs XIRR
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
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.
AGGREGATE
INSERT SYMBOL ALT IS -Wingdings
NPV, XNPV,IRR, XIRR
EXCEL GRAPH
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
you can define the format you need
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
IRR Vs XIRR
SUMIF vs SUMIFS
Syntax is reverse btw both of them
SUM VS SUBTOTAL
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.
AGGREGATE
INSERT SYMBOL ALT IS -Wingdings
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.
NPV, XNPV,IRR, XIRR
EXCEL GRAPH
Summary of 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