1. Add, Clear, and Remove Filter
To Add/Remove a filter:
Alt+D+F+F
Shift+Ctrl+L
To Clear Filter:
Alt+A+C
2. Create a Table
Advantages of inserting converting a range to the table:
Structured referencing
Automatic formula
Chart updates
How to convert a range to a table?
Select the data you need to convert and follow the below command or path.
Alt+N+T
Path: Insert — Table
How to convert your table back to the range?
Select the data you need to convert and follow the below command or path.
Alt+JT+G
Path: Design — Convert to Range
3. Move to the Edge of the data
How to move from one cell to another without the help of a mouse or scrolling?
Ctrl+Arrow Keys – Up, Down, Left, and Right respectively
To jump from start to end of a particular column or row
Shift+Ctrl+Arrow Keys – Up, Down, Left, and Right respectively
To select the data in the range
Ctrl+Home
To reach the 1st cell irrespective of where you are in the sheet
Ctrl+End
To reach the last cell irrespective of where you are in the sheet
4. Format Cells
How to format cells?
Format Cell Command box – Ctrl+1
Shortcut:
Shift+Ctrl+1 – Accounting Format
To remove decimal: Alt+H+0
To add decimal: Alt+H+9
Shift+Ctrl+2 – Time Format
Shift+Ctrl+3 – Date Format
Shift+Ctrl+4 – Default Currency (To change to foreign currency use Format Cell Command Box)
Shift+Ctrl+5 – Percentage Format
5. Auto Sum
3 ways to perform Sum Function:
Alt+=
Range Total (Alt+H+U+S):
You have to select the range you want to sum up + 1 blank row and column (As shown in the above video)
Offset:
Helps you add any additional row inserted between the sum range.
6. Paste as Values
Paste as Values is used to copy-paste only the final output and not the formula. If you simply paste the data as is, it will take the reference of the formula range.
Command to run Paste Special: Ctrl+Alt+V
7. Insert Chart in the same Sheet
How to insert a chart in the same sheet?
Alt+F1
To perform this action you need to select the range in which your sales data is there or the data which you want to present graphically.
By default, Excel will select the best suitable chart type as per your data. If you need to change the chart type, you will need to select the chart and click on the design to change the chart type.
8. Flash Fill
Flash Fill automatically fills your data when it senses a pattern.
Note: Flash Fill is only available in Excel 2013 and later.
Please like, comment, share, and subscribe…
Many more to come…
