18 Advanced Excel Tips and Tricks, Excel Secrets you don’t know

These are the advanced excel tips and tricks which I believe are less known. These are also few of my best excel tips. Please note that this tutorial is based on Microsoft Excel 2007. These techniques also work with other versions whether older or newer to 2007. But some of the options explained in this tutorial might be available in different locations in other versions of Excel.

Anyways I have tried to create a video which can best explain the steps and also I have put same in words who do not wish to watch the videos.

Advanced Excel Tips – Part 1 Video

Advanced Excel Tips – Part 2 Video

Ok here are my 18 Advanced Excel tips and tricks for you:

Advanced Excel Tips 1) Convert formula to value (but not using the paste special option)

Yes it’s just the other option

Press the F9 key to convert the formula to value. You are entering a new formula, press F9 before pressing the enter key.

And for existing formula, double click the cell or click the cursor in the formula bar and then press the F9 key

2) How to create multi-lined formula or add line breaks in formula for better readability.

Insert the cursor where you want line break in the formula and press Alt + Enter keys

3) Creating charts with keyboard shortcut

Click inside the data or table and press F11 key to create a chart in a chart sheet

Click inside the data or table and press Alt + F11 keys to create chart as an chart object

4) Delete cell contents using mouse

Select the cells you want to delete then take the cursor to the bottom right corner of the selection until you see a black plus. Then with the black plus drag the cursor to the upper right corner of the selection and then release the mouse button.

Note: You should see a dashed fill during the process

5) Startup file: Open a specific Microsoft Excel file or template automatically whenever you start or launch Microsoft Excel.

If you want to open a specific file during every excel launch then place that excel file inside Excel’s startup folder.

To place a file or template in the startup folder:

Step 1: Click the office button

Step 2: Then click on Excel options

Step3:  Then click on trust center

Step4: Then click on trust center settings

Step5: Then click on trusted locations

Step6: Then double click and copy the path of the trusted location

Step7: Then launch the windows run utility

Step8: Then paste the path and click ok

Step9: Now create a new file or copy an existing file to this location

Note: Files or templates placed in this location will be opened automatically at every launch of Excel application.

Advanced Excel Tips 6) Double click to move or navigate the cursor to first row or column

Double clicking the borders can move the cursor towards the beginning of the row or column. Please see the video for more details.

7) Create a number series in both x and y directions

Step1: To do this you need to create a number series in the top row and the first column in the series.

Step2: Then select the entire region

Step3: On the home tab click on fill dropdown and click on series

Step4: Select or check trend and click ok button

8) Delete non continuous blank cells in a list

Step1: Select the list

Step2: Then on the home tab click on find & select dropdown and click on Go To Special or press Ctrl + G and click on special button

Step3: Select blanks and click ok

Step4: On the home tab click delete dropdown and click on delete cells or press Ctrl + – keys (minus on the numeric keypad)

Step5: Select shift cells up and click ok button.

9) Create a number series with multiple

Follow the steps below to create a number series with multiples

Step1: Click on a blank or empty cell

Step2: Type the first value of the series, press enter and select the same cell

Step3: On the home tab click on fill dropdown and click on series option

Step4: Select columns and growth options

Step5: Type a step value and a stop value

Step6: Click ok button

Note: This kind to multiple series cannot be created by dragging. When you drag, a series of difference is created but not a series of multiples.

10) Copy visible cells

This is helpful especially when you hide the data and want to copy only visible data. When you normally copy and paste the data with hidden rows or columns, all the hidden data is also pasted.

To copy the visible data follow the steps below:

Step1: Select the data you want to copy

Step2: On the home tab click on find and select dropdown and click on GoTo Special

Step3: Select visible cells only and click ok

Step4: Now copy the data and paste wherever you want to

You won’t get the hidden data this time

11) How to group sheets and its advantages

Sheets’ grouping is helpful when you want to add same data across worksheets in one sweep. Actually there are two ways to fill the data across sheets.

To copy existing data across sheets:

Step1: Select the data which you want to fill across worksheets

Step2: Ctrl click or Shift click the sheet names

Step3: Then on the home tab click on fill dropdown and click on across worksheets

Step4: Select all and click ok

To copy new data

Step1: Ctrl click or Shift click the sheet names

Step2: Type or create the data. When sheets are grouped, whatever you type automatically passes through all the selected worksheets.

Note: Both the above methods requires you to ungroup the sheets when done.

To ungroup the sheets: Right click any of the worksheets and click on ungroup sheets.

Advanced Excel Tips 12) Copy top cells content to the blank cells below

Step1: Select the list

Step2: On the home tab select find and select dropdown and click on GoTo Special

Step3: Select blanks and click ok

Step4: Type = and press the up arrow key and press Ctrl + Enter keys

13) Save and open multiple workbooks with just 1 option

Step1: Open existing files or create multiple files

Step2: On the view tab click on save workspace and save the workbooks if prompted. This process will create a workspace file. This workspace file is similar to a hyperlink, which is linked to multiple files. When you open this file it opens all files which is linked with it.

Step3: Close all the open files and open the workspace file. You can see the workspace file opens all files which is linked with it.

14) How to create a unique list

Step1: Select the list or data

Step2: On the data tab click on advanced filter.

Step3: Do nothing for the list range as it will the address of the cells selected already.

Step4: For criteria select the heading of the list

Step5: Select copy to another location.

Step6: For copy to select the cell where you want the unique list generated.

Step7: Step unique records only and click ok

15) How to open macro files bypassing the security warning?

Caution: Do this only if you trust the file and only if you are sure that the file is free from virus.

All you need to do to bypass the security warning is just you have to place the macro file in one of the excel’s trusted locations.

To place the macro file in existing trusted locations:

Step1: Click on office button

Step2: Click on excel options

Step3: Click on trust center

Step4: Click on trust center settings

Step5: Click on trusted locations. Here you can see a list of trusted locations.

Step6: Now place the macro file in one of these locations. The next time you open this file you won’t see any warning.

Note: If you want to create a trusted location of your own, then click on add new location and place the macro files in that location.

16) How to use automatic scrolling feature in Excel?

To do this just press the middle button or the scroll wheel of the mouse once and move the mouse towards the direction you want to scroll. The page starts scrolling automatically until its stopped.

Note: When you press and leave the scroll wheel you should see four triangles pointed in four directions and a filled circle in the middle. After that, when you move the mouse in any of the four directions, you should see only one triangle with a filled circle.

17) New way to hide and unhide cells

This is really a new way to hide rows and columns

To hide columns or rows:

Select the columns or rows you want to hide then drag one of the column width or row height handle to towards the previous or next column or row.

Sorry it’s difficult to explain this so I request you to watch the advanced excel tips video part 1 and part 2 for clear instructions.

Advanced Excel Tips 18)  How to navigate from 1 sheet to another? A new method!

First o all define names to the first cells in all worksheets.

Doing so, you will find the defined names in the name box.

That’s it you are done. Now it’s pretty easy to navigate between worksheets when you have many worksheets say about even 50 or 100 of worksheets. Just use the name box dropdown and click on the sheet name that is the defined name. Doing so will take you to that sheet. You can move back and forth between sheets like this.

View Course Details

Ok, Hope you liked this tutorial on advanced excel tips. Please leave your comments and suggestions. Catch you soon with another interesting tutorial soon.