Have you been searching for Excel Advanced Tips and Tricks to make your work easier and faster?
Look no further: in this article we’ll show you 15 tips and tricks in Excel to save time and make you expert in Excel. So, let’s get started!
1 Keyboard Shortcuts
You’ll save lots of time and take quick actions once you master the keyboard shortcuts. Here are some of our favorite shortcuts:
- |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)
- |Ctrl + minus sign|: will give you Delete options
2 Copy Formula Down
To quickly copy a formula down:
- Drag the cursor to the bottom-right corner of the cell – you will notice the cursor has turned into thick black plus sign
- Double click the plus sign
3 Copying Values or Formulas
If you don’t want to drag a formula down to 1000 rows, try this:
Go to the first cell that you want to copy.
In the name box, type the address of the last cell where you want to fill the data and hit |Shift + Enter|
Press F2 to edit the formula in your first cell
Then hit |Ctr + Enter|
Above picture shows the name box highlighted in the red box. You will easily find this box right next to the fFormula Bar in the Excel
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 data that you want to check for duplicate (usually, |Ctrl + A| works)
- Click on the Data menu option from the menu ribbon
- Click on the Remove Duplicates button
- Choose whether your range has a header row
- Hit OK
6 Add Leading Zeros
You often need to add the zeros in the beginning of a number. For example, if you want to show the number 589 as 000000589 making it a text value of 9 characters. If your number is in A1 and you want to convert it with leading zeros with a maximum length of 9 characters, enter the formula in B1:
=TEXT(A1,REPT(“0”,9))
Note: you can change the value 9 to any number, if you want to increase the leading zeros.
7 Repeat Header for Printing
To repeat the header row(s) on every page while printing a multi-page sheet, follow the steps:
- Click on Page Layout menu option on the ribbon
- Click on Print Titles button
- Select the row(s) you want to repeat at the top in Rows to Repeat at Top box
8 Create Named Ranges
It is useful to give names to sheets with large number of rows, so that you can easily refer to these names without clicking and selecting long ranges. To do so:
- Click on the Formulas menu option on the ribbon
- Click on the Create from Selection button
- Select the ranges to give names from usually Top Row works well as this row is the header row
9 Copy Values
To copying only values and not the formulas, use this keyboard shortcuts:
- |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
Practice this a couple of times and then do it in front of your friends, without any doubt they will be shocked by this new ninja technique.
10 Import a Table from the Web
Sometimes you need to import data from the internet into your excel and you wish for a better way of doing so. Well, here is trick. Follow the below steps to do so:
- Click on Data menu option from the ribbon
- Click 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 window
- Scroll down to the table of home runs
- Click on Click to select this table check box
- Click on Import
- Click on OK
Only an Excel expert can import raw tables from the web to the Excel files!
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 statistics, such as Average, Count, Max, Min, Numerical Count, and Sum of the data without entering any formula. To show these statistics right click on the bottom toolbar and choose the desired statistic.
14 Text to Columns
Did you have a data in the text document and you wish to copy that to Excel? Let’s say you have a Word file with some bullet points data, like this:
Vendor A: 5,000
Vendor X: 9,900
Vendor D: 10,000
Vendor Z: 3,500
To copy and keep the desired formatting for easier process, follow these steps:
- Copy the data from your Word document
- Paste the data in a blank Excel spreadsheet (say starting from cell |A1|)
- Select column A
- Click on the Data menu on the ribbon
- Click on Text to Columns button
- In the Convert Text to Columns Wizard — Step 1, select “Delimited”
- In Step 2, check the “Other” box and enter |:| (colon) as the delimiter.
- Hit Next/Finish and complete the wizard
15 Count Unique Values From a Range
If you want to count the unique values in a cell, a quick formula is sometimes all you need.
Let’s say you have some text values in A1:A10 range. We have added some city names in this range. I repeated some cities for this example.
In another empty cell, in this case, in C1, we type this formula:
=SUM(1/COUNTIF(A1:A10,A1:A10))
But instead of hitting the regular enter key, we use Command + Shift + Enter on a Mac or Control + Shift + Enter on Windows. By doing so, we create a formula. You should see the number 7 as the result of this formula.
