James Richard

# 15 Excel Advanced Tips and Tricks You Need to Know

Updated: Nov 16, 2022

## 1. Keyboard Shortcuts

You will save tons of time and make moves like a ninja once you master the __advanced Excel skill __of keyboard shortcuts. Here are some of my favorites:

|Ctrl + A|: will select all of the data

|Ctrl + C|: will copy the selected data (or object)

|Ctrl + V|: will paste the copied data (or object)

|Ctrl + End|: will take you to the last cell of your data

|Ctrl + Home|: will take you to the first cell of your data

|Ctrl + Up Arrow|: will take you to the first cell of your active column

|Ctrl + Down Arrow|: will take you to the last cell of your active column

|Shift + Space|: will select row(s) of your active cell(s)

|Ctrl + Space|: will select column(s) of your active cell(s)

|Shift + Space|: will select row(s) of your active cell(s)v

|Ctrl + minus sign|: will give you Delete options

## 2. Copy Formula Down

To quickly copy a formula down:

Hover to the bottom-right corner of the cell with the formula (you will notice that the cursor has turned to a thick black plus sign)

Double click the plus sign

## 3. Copying Values or Formulas

When tip #2 doesn’t work because there’s a blank cell between your starting cell and the end cell of your range or you don¢t want to drag a formula down to 1000 rows, try this:

Go to the first cell that you would like to copy or fill in other cells

In the

**name box**, type in the address of the last cell of the range, where you want to fill data or formula and hit |Shift + Enter|Press

**F2**to edit the formula in your first cellThen hit |Ctr + Enter|

You can see the name box highlighted in the red box. You will find this box right next to the Formula Bar

## 4. Total a Column or a Row

To quickly total a column or a row, in the last cell, hit |Alt + =| (equal to sign).

## 5. Delete Duplicate Rows

To delete rows with duplicated data, follow these steps:

Select the range of the data that you want de-duplicated (usually, |Ctrl + A| works)

Click on the

**Data**menu option from the menu ribbonClick on the

**Remove Duplicates**buttonChoose whether your range has a header row

Hit

**OK**

## 6. Add Leading Zeros

You often find the need to add leading zeros to a number and most likely the result value is a text format. For example, you may want to show the number 7893 as 0000007893, making the number a text value with a length of 10 characters. If your number is in A1 and you want to convert that to text with leading zeros with a maximum length of 10 characters, enter this formula in B1:

=TEXT(A1,REPT("0",10))

## 7. Repeat Header for Printing

While printing a multi-page sheet, it is useful to repeat the header row(s) on every page. To do so,

Click on

**Page Layout**menu option on the riboonClick on

**Print Titles**buttonSelect the row(s) you want to repeat at the top in

**Rows to Repeat at Top**box

## 8. Create Named Ranges

For sheets with large number of rows, it is useful to give names to ranges so that you can refer to these names in your formulas without clicking and selecting long ranges. To quickly give names to your ranges:

Click on the

**Formulas**menu option on the ribbonClick on the

**Create from Selection**buttonSelect the ranges to give names from usually

**Top Row**works well as this row is the header row

## 9. Copy Values

To speed up copying only values and not the formulas, use this keyboard shortcut sequence:

|Ctrl + A| to select the whole range of data (or select your range with the help of the mouse)

|Ctrl + C| to copy the data

in your destination, press |Alt + E|, then |S|, then |V|, and then |Enter|

The whole sequence is:

`Ctrl + A, Ctrl + C, Alt + E, S, V, Enter`

After you practice this a couple of times, when you do this in front of your peers, without a doubt they will be in awe of your new ninja abilities.

## 10. Import a Table from the Web

Often you need to import data from the internet and you wish for a better way of doing so. Well, here is one way. Lets say you want to import the list of all time leaders of home run hitters in baseball from baseball almanac. Follow these steps to enjoy seeing this data in an Excel file:

Click on

**Data**menu option from the ribbonClick on

**From Web**In the browser window, enter the URL: http://www.baseball-almanac.com/hitting/hihr1.shtml

Hit the

**Go**button on the browser windowScroll down to the table of home runs

Click on

**Click to select this table**check boxClick on

**Import**Click on

**OK**

## 11. Delete Current Row(s)

To quickly delete current row(s), follow these two steps

Select the row(s) by |Shift + Space|

Delete the selected row(s) by |Ctrl + - (minus sign)|

## 12. Delete Current Column(s)

To quickly delete current column(s), follow these two steps

Select the column(s) by |Ctrl + Space|

Delete the selected column(s) by |Ctrl + - (minus sign)|

## 13. Quick Aggregation

Quick Tool provide aggregate statistics, such as **Average, Count, Numerical Count, Max, Min, and Sum** of the data from a selected range without entering any formula. To show these statistics in the bottom toolbar, right click on the toolbar and choose the desired statistic.

## 14. Finding a Related Value

Often you have to translate or cross-walk a value, say a state code, to it a related value, in this case the fully spelled state name. You can write multiple, nested |IF|, but a ninja way is to use a |VLOOKUP| formula. The syntax of VLOOKUP formula is:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

In the above example, our lookup value is the state code and table array is a table with the state codes and their descriptions.

The column index is the column that we want to return after matching a code

The range lookup is an argument to search for exact matches (1 or TRUE) or approximate matches (0 or FALSE)

Let’s say, your customer IDs in Column A, their mailing state code in Column B, and in Column C you want to see the state spelled out. To do so, follow these steps:

Create a table of codes and their fully-spelled values in Column E and F

In |C2|, enter this formula: =VLOOKUP(B2,E3:F6,2,0)

Copy the formula down

Here’s how it looks:

## 15. Converting Numbers to Ranges

Did you ever find yourself with the need to convert a number column to discrete ranges for easier summaries or graphs? Did you wish there was a better way? Well, there is: the |VLOOKUP| formula. You saw the syntax: The syntax of VLOOKUP formula is:=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) To convert numbers to ranges, you use the |range_lookup| argument with a value of |TRUE|, which will tell Excel to make an approximate match to the lookup value. We can use this to our advantage. Let’s say, you have some measure, such as, population, revenue, sales, # of units, etc, in Column A. You want to convert these measures to certain ranges. To do so, follow these steps:

Create a table of measures and their discrete ranges in Column F and G

In |C2|, enter this formula: =VLOOKUP(A2,F3:G8,2,1)

Copy the formula down

Here’s how it looks: