13 Excel Tips and Tricks to Make You Into a Pro

Microsoft Excel is an application with hundreds of tools and endless possibilities. This may be quite intimidating for users just starting out with the software. The tips and tricks below all aim to make your experience using the application better and faster. It doesn’t matter how much experience you have with Microsoft Excel; we’re here to help you increase your productivity and efficiency when creating new projects.

Microsoft Excel Tips and Tricks

Note: The tips below are written for MS Excel 2016 or later. Earlier versions of Microsoft Excel might require different steps or have missing features.

Let’s see how you can become a professional Excel user by simply utilizing 13 tips and tricks.

1] Add multiple rows and columns at once

how to insert multiple columns at once in Excel

Spreadsheeting is the most common thing you’ll do in Excel. Whenever creating spreadsheets, you’ll be adding new rows and columns, sometimes hundreds at once. This simple trick makes it easy to do it in a split second rather than having to add new entries manually.

This is how to add multiple rows or columns at once in Excel.

1. Highlight the same amount of rows or columns as you want to insert. The rows and columns can have data inside them — this won’t affect the new cells.
2. Right-click on any of the selected cells, then click Insert from the context menu.
3. The new, empty cells will appear above or to either side of the first cell you originally highlighted. This can be selected.

As you can see in the example below, creating new cells in bulk is extremely easy and will save you time.

2] Apply filters to your data

Filtering which type of item is being shown in an Excel spreadsheet

When working with sets of data, simplifying it may help you see patterns and other crucial information you need. Browsing through hundreds of rows and columns for specific data entries only leaves room for error. This is where Excel’s data filters are a huge help.

As the name suggests, filters help you curate your data and only show you entries that fit specific criteria. For example, if you have a long list of customers but you only want to display people from California, apply a filter. It’ll hide each customer entry that specifies a different location. It’s that easy!

Let’s see how you can implement filters in your Excel spreadsheets.

1. Open the spreadsheet you want to filter.
2. Click on the Data tab from the Ribbon, and select Filter. This will turn on filtering for your entire spreadsheet, which you can notice by the added drop-down arrows for each header.
3. To filter the results by a specific column, just click on its drop-down arrow. You should see a new window pop up.
4. Select how you want your data to be organized. Remove the tick from any heading you don’t want to display and customize how Excel sorts your remaining data.

3] Remove duplicate entries with one click

Remove duplicate cells in Excel.

Especially in large sets of data, you might have some unwanted duplicate entries. While it’s possible to manually find these entries, there’s a much faster way to remove them without leaving anything behind. Let’s see how.

1. Highlight the row or column you want to remove duplicates from.
2. Locate the Remove Duplicates button in the Tools subtab of the Data ribbon tab. To confirm which data to use, a new window will pop up.
3. Click on the Remove Duplicates button. Voila!

4] Use conditional formatting

The Excel feature of conditional formatting

When working with data sets, conditional formatting is an invaluable visual aid. It takes into account the contents in a cell and the criteria you provide to modify its color. Make heat maps, color-code frequently used data, and a whole lot more with this. Let’s take a look at how you may begin adding this functionality to your spreadsheets.

1. To work with a specific set of cells, highlight them.
2. To access Conditional Formatting, first go to the Ribbon and click on the Home tab.
3. Customize Excel’s treatment of each cell by selecting the logic you wish to use from the drop-down menu or by creating your own rule.
4. To instruct Excel on how to properly organize your data, a new window will pop up. When you’re through, click OK to see your results.

5] Import a table from the internet

Insert table from the web in Excel (Source: Analytics Tuts)

Excel allows you to quickly import tables from the internet without having to worry about formatting or janky cells. Let’s take a look at how you can do this straight from the software itself.

1. Switch to the Data tab from the Ribbon, then click on the From Web button.
2. In the web browser, enter the website URL that contains the table you want to insert. For example, we’ll be working with an Excel Sample Data table from Contextures.
3. Hit the Go button. Once the site loads, scroll down to the table you want to insert.
4. Click on the Click to select this table checkbox.
5. Click on the Import button.
6. Click on OK. The table should appear in your spreadsheet as cells.

6] Speed up your work process with AutoFill

With Excel’s AutoFill

It doesn’t take long for entering a string of repeated data to get boring. The usage of AutoFill can help with this. An easy-to-implement function that can detect dates and other repeated patterns and fill in blank cells automatically is available. Use it as follows.

1. Pick out the cells that will serve as your template.
2. Tap the plus sign (+) that appears when you hover over a cell’s bottom right corner to access the AutoFill feature. The AutoFill feature is indicated by this.
3. To keep clicking, press and hold the mouse button while dragging the pointer downward. Ideally, the expected facts will appear as you drag the mouse.
4. Once you are happy with the entries, release the mouse.

7] Easily insert a screenshot

Open Excel and insert a screenshot (Source: WebNots)

Excel can be used to incorporate a snapshot of an open application without requiring you to go through the sometimes difficult procedure of storing one. This easy-to-use function allows you to capture a screenshot of any open window and import it into Excel right away.

Simply click the Insert tab and choose the Screenshot option to use it. All you have to do is select one of your open windows.

8] Make sure your spreadsheet has checkboxes.

Excel spreadsheets now have an interactive checkbox included.

You can include checkboxes in your Excel spreadsheets, did you know that? You do now. This is the way you do things. Verify that the Ribbon’s Developer tab is activated!

1. Select the cell where a checkbox is to be added.
2. Navigate to the Ribbon’s Developer tab.
3. In your sheet, add a checkbox or an option button based on what you require.

9] Make use of Excel shortcuts.

The extensive set of features and operations offered by Excel continues to garner the praise it deserves. In order to become an expert Excel user, knowing how to use keyboard shortcuts is crucial. To take your Excel skills to the next level, here are a few of the most popular shortcuts:

• To choose a column, click on a cell and then hit the Ctrl and Space keys.
• Choose a row: After selecting several cells at once, hit the Shift and Space keys.
• In a cell, begin a new line: To begin a new line while typing, use Alt + Enter.
• Put the time now here:: Ctrl + Shift + Colon (;) must be pressed.
• Add the current day here: Hold down the Ctrl + Colon (;) keys.
• Use the Ctrl + 0 keys to hide a column.
• To hide a row, hit the Ctrl + 9 shortcut.
• Formulas can be shown or hidden by using the Ctrl + Tilde (~) keys.

10] Get rid of spreadsheet gridlines

Any Excel spreadsheet may have its grid lines removed at the press of a button, leaving you with a clean slate. In the Show part of the Ribbon, after you’ve chosen the View tab, deselect the Gridlines option.

11] Insert a pivot table quickly.

Using pivot tables to analyze and present your data is a great idea. After making sure that every cell is selected, select Recommended Pivot Tables under the Insert menu to start creating a pivot table.

12] Include a note in your formulas.

Adding comments to your formulas can help users better comprehend them or make them easier to search. Just add + N(“Comment here”) after your formula to add a comment. You can see this in the formula bar, but it will not appear in the actual cell.

13] Sketch out your equations.

Excel transcribes your handwritten formulae (Source: HowToGeek)

You can draw equations and have Excel turn them into text in Excel 2016 and later.

1. Navigate to the Ribbon’s Insert tab.
2. Choose Ink Equation after clicking on Equation.
3. Put your Equation in the box that is.

13 Excel Tips and Tricks – Conclusion: 

Consider signing up for our newsletter if you are seeking more tech-related articles to read or if you are looking for more guides such as this 13 Excel Tips and Tricks blog. To assist you, we frequently release guides, news pieces, and tutorials.