5 Simple Excel Tricks Worth Using

Excel is one of the tools I use daily for performing both basic and advanced calculations, as well as building various reports for business users. How can you easily improve your work using Excel? In this article, you will learn:
  1. How to set up automatic header printing?
  2. How to quickly convert numeric values to percentages?
  3. How to quickly filter data?
  4. How to add shortcuts to the quick access toolbar?
  5. How to freeze the first row with headers?

How to set up automatic header printing?

Kiedy posiadamy tabelę, której nie możemy wydrukować na jednej stronie warto wykorzystać w tym celu nazwane zasięgi. Po zaznaczeniu wiersza, który chcemy zawsze drukować wystarczy wpisać nazwę zasięgu ‘print_titles’ jak na zrzucie poniżej.

When you have a table that can’t fit on one page, consider using named ranges for automatic header printing. After selecting the row you want to print consistently, simply enter the range name ‘print_titles,’ as shown in the screenshot below.

Now, let’s look at the print preview. We’ve selected the option to print the entire active sheet, and on the second page of the preview, you can see that our headers will also be printed. Simple, isn’t it?

How to quickly convert numeric values to percentages?

Another solution is a quick conversion of numeric values to percentages. Consider the example below. We have a set of products and their associated profit margins expressed in integers. When you simply select the cell and click on the percentage formatting option, the number 13 will turn into 1300%. While this is correct, as 1 equals 100%, how do we convert 13 to 13%, and the remaining numbers easily?

Copying and pasting special comes to the rescue. Enter the value 1% in any cell, then click copy (or CTRL+C). The next step is to select the cells you want to convert to percentage values. After selecting the cells, right-click and choose Paste Special from the menu. This will bring up a context menu where you choose the Multiply option, indicating multiplying the copied value by the selected values. Click OK, and your solution is ready.

How to quickly filter data?

Filtering tables with a large amount of data can be cumbersome for some users. So, how do you easily and quickly filter data in a table? Consider the example below. We have a table containing about 120,000 sales records.

Let’s say we want to filter the CountryName column and select only the United States. Simply right-click on any cell with the value ‘United States,’ then choose Filter and Filter by Selected Cell’s Value (shortcut keys E + V).

As a result, our table has been filtered in the CountryName column to show only the United States values.

How to add shortcuts to the quick access toolbar?

Excel has many functions hidden under specific ribbons. If you frequently use some functions, it’s worth setting up your ribbon so that the most commonly used functions are easily accessible. The quick access toolbar comes in handy, and you can customize it to your needs. Suppose you often create pivot tables to analyze your datasets. To save yourself a few clicks, add this function to the quick access toolbar. Click on the function in the ribbon and choose ‘Add to Quick Access Toolbar.

After selecting the function and adding it to the quick access toolbar, your function will be added to the top bar, as shown in the screenshot below. Now you only need one click to add, for example, a pivot table without unnecessary searching in the ribbon.

How to freeze the first row with headers?

Browsing large tables can be tedious if the sheet is not formatted as a table but rather contains raw data. Scrolling down makes the top values disappear, and we might forget what a particular header meant. So, how do you freeze headers so that they don’t scroll along with the other cells? The Freeze Panes feature comes in handy. Simply go to the View ribbon and choose Freeze Panes, then select Freeze Top Row. Now, you can freely scroll the sheet down, and the headers will remain visible.

Summary

This article has described five simple Excel functionalities:

  1. Setting up automatic header printing.
  2. Converting numeric values to percentages using paste special.
  3. Filtering data with two clicks.
  4. Using the quick access toolbar.
  5. Freezing headers in an unformatted table.

Interesting article? Share further!

Leave A Comment