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

- Written by Puneet

excelchamps-free-courses-puneet-gogia

(Excel Tip-1) Select Formula Cells

Let’s say you want to convert all the formulas into values and the cells where you have formulas are non-adjacent. So instead of selecting each cell one by one, you can select all the cells where you have a formula. Here are the steps:

  1. First, go to Home Tab ➜ Editing ➜ Find & Select ➜ Go To Special.
  2. In the “Go To Special” dialog box, select formulas and click OK.
excel-tips-tricks-select-formula-cells

(Excel Tip-2) Multiply using Paste Special

To do some one-time calculations you can use the paste special option and save yourself from writing formulas.

excel-tips-tricks-multiply-paste-special

(Excel Tip-3) Highlight Duplicate Values

Well, you can use a VBA code to highlight values but the easiest way is to use conditional formatting. Here are the steps you need to follow:

excel-tips-tricks-highlight-duplicate-values-conditional-formatting
  1. First of all, select the range where you want to highlight the duplicate values.
  2. After that, go to Home Tab ➜ Styles ➜ Highlight Cells Rule ➜ Duplicate Values.
  3. Now from the dialog box, select the color to use and click OK.

Once you click OK, all the duplicate values will get highlighted.

(Excel Tip-4) Quick Analysis Tool

If you ever noticed that when you select a range of cells in Excel, a small icon at the bottom of the selection appears? This icon is called the Quick Analysis Tool.

excel-tips-tricks-quick-anakysis

When you click on this icon you can see some of the options which are there on the ribbon which you can directly use from here to save time.

(Excel Tip-5) RUN Command

Yes, you can also open your Excel application using the RUN command.

excel-tips-tricks-open-with-run-command
  • For this, all you have to do is open RUN (Window Key + R) and then type “excel” into it.
  • In the end, hit enter.

(Excel Tip-6) Open Specific File

I’m sure like me you also have a few or maybe one of those kinds of workbooks that you open every day when you start working on Excel. There is an option in Excel which you can use to open a specific file(s) whenever you start Excel in your system. Here are the steps.

excel-tips-tricks-open-files-when-open-excel
  1. Go to File ➜ Options ➜ Advanced ➜ General.
  2. In general, enter the location (yes, you have to type) of the folder where you have those file(s) in “At startup open all the files in”.

(Excel Tip-7) Open Excel Automatically

Whenever I “Turn ON” my laptop the first thing I do is open Excel and I’m sure you do the same thing. Well, I’ve got a better idea here, you can add Excel to your system’s startup folder.

excel-tips-tricks-add-excel-start-up-forlder
  1. First, open “File Explorer” by using the Windows key + E.
  2. Now, enter the below address into the address bar to open the folder (change the username with your actual username).
    • C:UsersUserAppDataRoamingMicrosoftWindowsStart MenuProgramsStartup
  3. After that, open the Start Screen, right-click on the Excel App, and click Open file location.
  4. From the location (Excel App Folder), copy the Excel App icon and paste it into the “Startup” folder.

Now whenever you open your system, Excel will automatically start.

(Excel Tip-8) Smart Look Up

In Excel, there is an option called “Smart Lookup” and with this option, you can look up a text on the internet. All you have to do is, select a cell or a text from a cell, and go to Review ➜ Insights ➜ Smart Lookup.

excel tips tricks smart lookup press button

Once you click on it, it opens a side pane where you’ll have information about that particular text which you have selected. The idea behind this option is to get information by seeing definitions, and images for the topic (text) from different online sources.

(Excel Tip-9) Screen Clipping

Sometimes you need to add screenshots to your spreadsheet. And for this, Excel has an option that can capture the screen instantly, and then you can paste it into the worksheet. For this go to ➜ Insert ➜ Illustrations ➜ Screen Clipping.

excel tips tricks clip screen

Related – Excel Camera

(Excel Tip-10) Locate a Keyboard Shortcut

If you use Excel 2007 to Excel 2016, then you can locate a keyboard shortcut by pressing the ALT key. Once you press it, it shows the keys for the options which are there on the ribbon, just like below.

excel-tips-tricks-locate-keyboard-alt

Let’s say, you want to press the “Wrap Text” button, and the key will be ALT H W. In the same way you can reach all the options using the shortcut keys.

Related – Insert Row

Last Updated: February 28, 2024