15-excel-tips-and-tricks

15 Advanced Excel Tips and Tricks That Can Make You Expert

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!

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:

  1. |Ctrl + A|: will select all of the data
  2. |Ctrl + C|: will copy the selected data (or object)
  3. |Ctrl + V|: will paste the copied data (or object)
  4. |Ctrl + End|: will take you to the last cell of your data
  5. |Ctrl + Home|: will take you to the first cell of your data
  6. |Ctrl + Up Arrow|: will take you to the first cell of your active column
  7. |Ctrl + Down Arrow|: will take you to the last cell of your active column
  8. |Shift + Space|: will select row(s) of your active cell(s)
  9. |Ctrl + Space|: will select column(s) of your active cell(s)
  10. |Shift + Space|: will select row(s) of your active cell(s)
  11. |Ctrl + minus sign|: will give you Delete options

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

excel1

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|

excel2

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

Total a Column or a Row

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

excel3

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

excel4-1 excel4-2

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.

excel5

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

excel6

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

excel7

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

excel8-1 excel8-2 excel8-3

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.

excel9

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

excel10

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.

excel11

Leave a Reply

Your email address will not be published. Required fields are marked *