top of page
Maveristic

Be Valuable, Not Available

Writer's pictureCanute Fernandes

Helpful Excel Shortcuts

1. Add, Clear, and Remove Filter


To Add/Remove a filter:

  1. Alt+D+F+F

  2. Shift+Ctrl+L

To Clear Filter:

  1. Alt+A+C

 

2. Create a Table


Advantages of inserting converting a range to the table:

  1. Structured referencing

  2. Automatic formula

  3. Chart updates

How to convert a range to a table?

Select the data you need to convert and follow the below command or path.

  1. Alt+N+T

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

  1. Alt+JT+G

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

  1. Ctrl+Arrow Keys – Up, Down, Left, and Right respectively

  2. To jump from start to end of a particular column or row

  3. Shift+Ctrl+Arrow Keys – Up, Down, Left, and Right respectively

  4. To select the data in the range

  5. Ctrl+Home

  6. To reach the 1st cell irrespective of where you are in the sheet

  7. Ctrl+End

  8. To reach the last cell irrespective of where you are in the sheet

 

4. Format Cells


How to format cells?

  1. Format Cell Command box – Ctrl+1

  2. Shortcut:

  3. Shift+Ctrl+1 – Accounting Format

  4. To remove decimal: Alt+H+0

  5. To add decimal: Alt+H+9

  6. Shift+Ctrl+2 – Time Format

  7. Shift+Ctrl+3 – Date Format

  8. Shift+Ctrl+4 – Default Currency (To change to foreign currency use Format Cell Command Box)

  9. Shift+Ctrl+5 – Percentage Format

 

5. Auto Sum


3 ways to perform Sum Function:

  1. Alt+=

  2. Range Total (Alt+H+U+S):

  3. You have to select the range you want to sum up + 1 blank row and column (As shown in the above video)

  4. Offset:

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

48 views0 comments

Comments

Couldn’t Load Comments
It looks like there was a technical problem. Try reconnecting or refreshing the page.