Top Excel Tips and Tricks (HACKS) to Make You a PRO (Free PDF) [PART-6]

(Excel Tip-1) Tracking Important Cells

Sometimes we need to track important cells in a workbook and for this, the best way is to use the watch window. In the watch window, you add those important cells and then get some specific information about them in one place (without navigating to each cell).

• First, go to Formula Tab ➜ Formula Auditing ➜ Watch Window.

• Now in the “Watch Window” dialog box, click on “Add Watch”.

• After that select the cell or range of cells that you want to add and click OK.

Once you hit OK, you’ll get some specific information about the cell(s) in the watch window.

(Excel Tip-2) Flash Fill

Flash fill is one of my favorite options to use in Excel. It’s like a copycat, perform the task which you have performed. Let me give you an example.

Here are the steps to use it: You have dates in the range A1: A10 and now, you want to get the month from the dates in the B column.

All you need to do is to type the month of the first date in cell B1 and then come down to cell B2 and press the shortcut key CTRL + E. Once you do this it will extract the month from the rest of the dates, just like below.

(Excel Tip-3) Combine Worksheets

I’m sure somewhere in the past you have received a file from your colleague where you have 12 different worksheets for 12 months of data. In this case, the best solution is to combine all of those worksheets using the “Consolidate” option, and here are the steps for this.

• First, add a new worksheet and then go to Data Tab ➜ Data Tools ➜ Consolidate.

• Now in the “Consolidate” window, click on the upper arrow to add the range from the first worksheet and then click on the “Add” button.
• Next, you need to add references from all the worksheets using the above step.

• In the end, click OK.

(Excel Tip-4) Protect a Workbook

Adding a password to a workbook is quite simple, here are the steps.

• While saving a file when you open a “Save As” dialog box go to Tools General Options.

• Add a password to “Password to Open” and click OK.

• Re-enter the password and click OK again.
• In the end, save the file.

Now, whenever you re-open this file it will ask you to enter the password to open it.

(Excel Tip-5) Live Image

In Excel, using a live image of a table can help you resize it according to space, and to create a live image there are two different ways in which you can use it.

One is camera tools and the second is the paste special option. Here are the steps to use the camera tool and for paste special use the below steps.

1. Select the range you want to paste as an image and copy it.
2. Go to the cell and right-click, where you want to paste it.
3. Go to Paste Special ➜ Other Paste ➜ Options Linked Picture.

(Excel Tip-6) Userform

A few of the Excel users know that there is a default data entry form is there which we can use. And the best part is there is no need to write a single line of code for this.

Here’s how to use it:

1. First of all, make sure you have a table with headings where you want to enter the data.
2. After that select any of the cells from that table and use the shortcut key Alt + D + O + O to open the user form.

(Excel Tip-7) Custom Tab

We all have some favorite options or some options which we use frequently. To access all those options in one place you create a tab and add them to it.

1. First, go to File Tab ➜ Options ➜ Customize Ribbon.
2. Now click on “New Tab” (this will add a new tab).
3. After that right-click on it and name it and then name the group.
4. Finally, we need to add options to the tab and for this go to “Choose Commands From” and add them to the tab one by one.
5. In the end, click OK.

(Excel Tip-8) Goal Seek

In simple words, Goal Seek is a problem-solving tool. It helps you find the input value by proving the value you want in the result.

…here is the complete guide to learning about it.

(Excel Tip-9) Text to Speech

This is an option where you can make Excel speak the text you have entered into a cell or a range of cells.

(Excel Tip-10) Named Range

To create a named range the easiest method is to select the range and create it using the “Create from Selection” option. Here are the steps to do this:

• Select the column/row for which you want to create a named range.
• Right-click and click on “Define name…”.

• Select the option to add the name for the named range and click OK.

That’s it.