8 Lesser-Known Excel Features & Tricks

Microsoft Excel is a powerful tool for managing, organizing, and visualizing large datasets. However, if you aren’t using at least some of the available shortcuts and time-saving tricks when working with spreadsheets, you aren’t fully optimizing this application. This article will explore eight Excel tricks to help you work faster and smarter when using spreadsheets.

8 Lesser-Known Excel Features & Tricks

Excel is one of the core tools used in most businesses across industries. While some appreciate this application’s extensive capabilities for managing, reporting on, and visualizing data, others struggle to move past basic Excel functions, such as sorting data into basic columns and rows. The more time you spend learning some of this app’s basic features, tools, and shortcuts, the easier it will be to work with both small and large datasets. 

The following are eight tips that will save you time and hassle when working with Excel:

• Only use the mouse if you have to. Selecting ALT will provide a list of all Excel keyboard shortcuts. This allows you to navigate this application only using the keyboard.

• Use F9 to check the results of partial formulas. When performing this trick, be careful; if you click on the F9 key but don’t select a portion of your formula, the formula will be irreversibly replaced with the resultant value. In order to use F9 to check partial formula results:
  1. Select the range of a formula. The numbers from that range will show up rather than cell references.
  2. The results from the selected parts will then appear, which can be used to evaluate the formula.

• Quickly delete blank cells. When reviewing a worksheet, you may realize you have multiple blank cells that have to be removed. Instead of deleting each blank cell manually, which can take hours:

1. Select the range of cells you’d like to remove.
2. Go to the Home tab.
3. Under the “Editing” section, click on “Find & Select,” then “Go To Special.”
4. Select “Blanks.”
5. Click “OK.”
6. From the Home tab, go to the “Cells” group and click on “Delete.”
7. This will erase any blank cells.

• Use F4 to lock cells. If you need to copy a formula into Excel, sometimes you may wish to input cells that will move with the formula. However, other times you may not want the cells to move along with the formula. To lock one of the inputs, oftentimes Excel users place dollar signs before the row number and column letter. This is not necessary, though, and can be a waste of time. Instead of using dollar signs, choose the cell you’d like to lock:
  1. Select F4, which will not only insert a dollar sign but also lock the cell.
  2. You can also continue to click on F4 to cycle through various options, such as lock cell, lock column letter, lock row number, and no lock.

• Add a diagonal line to a cell. If you need to insert a diagonal line into a cell, the border function can be a helpful tool:

1. Select the cell into which you’d like to insert a diagonal line.
2. Right-click on the cell and choose “Format Cells.” 
3. A dialogue box will appear. Select “Border” from the top ribbon of tabs.
4. Select a diagonal line from the options.
5. Preview the resulting diagonal line in the box.

• Add multiple columns or rows at one time. In some instances, you may have to add more than one column or row at a given time. One example would be if you were looking to add two rows in the middle of a dataset. In order to do so:

1. Choose two (or more) rows that start with the one right below where you wish to place the new row.
2. Right-click, then click on “Insert” in the dropdown menu.
3. The newly created rows will show up above the first selected row. 
4. These steps can also be used to add columns.

• Import a table from the internet. It’s sometimes necessary to import or insert a table into an Excel worksheet directly from an online source. To insert a table:

1. Copy the website’s URL
2. Choose “Data” > “Get & Transform” > “From Web.”
3. Click on “CTRL + V,” which will paste the URL into the box.
4. Choose “OK.”
5. From the “Navigator” pane that’s located beneath “Display Options,” click on the “Results” table.
6. If you select the pane on the righthand side, it’s possible to preview results.
7. Results can be loaded through Power Query. This changes the data and loads it into an Excel table.

• Make formulas that are easier to understand. When using an Excel spreadsheet that has multiple formulas, or even just a handful of complicated formulas, it can become confusing to see long trains of numbers and symbols and know what is what. The good news is that there’s an easy solution. Names such as “Coefficient” or “Reference Number” can be assigned to cell groups to add clarification. These names can then be used in formulas. If you want to create easier-to-remember formula names:

1. Choose the cells you’d like to name.
2. Select “Formulas” > “Define Name.”
3. From there, you can input the name you’ve created (make sure it doesn’t contain numbers).
4. Click on “OK.”
5. If, for example, you call a cell “Reference Number,” this name can then be used in a formula by typing “=Reference Number*A2” or some variation on this formula. This provides a fast way to locate these formulas in the ribbon.

These eight Excel tricks cover just a few of the many time-saving shortcuts that can save you hours when creating or navigating worksheets and workbooks. The more of them you know, the easier it will be to work with Excel.

Learn More About Excel with Hands-On Classes

Noble Desktop currently offer a variety of Excel courses in NYC and live online. Classes are available for those who are new to Excel, as well as learners who regularly work with this spreadsheet application and wish to brush up on their skills. In addition, there are also in-person and live online Excel courses available through Noble Desktop or one of its affiliate schools. A variety of course options are offered, ranging in duration from three hours to two days and costing between $229 and $1,099. 

Noble Desktop’s Excel Bootcamp provides a great option for those who want to master core Excel concepts, such as working with VLOOKUP and PivotTables, as well as performing What-If Analysis. Students who enroll in this rigorous, 21-hour course can elect to study in person in Manhattan or learn in the live online environment. This small class comes with the option of a free retake and covers a variety of Excel concepts applicable to the business world. 

Those interested in studying close to home can also browse over 400 in-person Microsoft Office classes in a city near you to find local Excel study options.