Technology

15 Must-Know Excel Tricks for Accountants and Office Professionals

Excel is a widely used software tool among office workers. However, not everyone knows how to maximize their productivity while minimizing their working time. In this article, FPT Shop will reveal some incredibly useful Excel tricks to help you achieve that.

Administrator

14 December, 2023

147

Excel is considered one of the most useful tools and widely used in administration – office. If you know how to apply the features of this tool well, you will save a lot of time and increase productivity in your work. This article from FPT Shop will summarize the best Excel tricks that any accountant or office worker should not miss!

15 extremely useful Excel tricks for office workers

When first starting with Excel, not everyone knows about these tricks to achieve high efficiency and convenience in their work. Below are 15 extremely useful Excel tricks for office workers that we have collected and used a lot in our work, so you can refer to them!

Convert rows to columns and columns to rows

To convert rows to columns and vice versa, you follow the following sequence:

Select the entire data of that row, then right-click and choose Copy or press the Ctrl + C key combination. Then, select any cell -> Right-click and choose Paste Special -> Select Transpose. Note: You can’t convert data from a row to a column at the same time, but need to manually convert each row.

Delete duplicate data

To delete duplicate data in Excel, you select the data area that needs to delete duplicate data, then go to the Data tab -> Select Remove Duplicates.

Open multiple Excel files at the same time

While working, there will be times when you want to open multiple spreadsheets at the same time for ease of comparison and cross-referencing information between sheets. To open multiple Excel files at the same time, you can do the following:

Press and hold the Ctrl key -> Select the Excel files you want to open, then press Enter.

Copy data easily

To copy data in Excel extremely simple and fast, you just need to perform the following steps:

Select the entire data you want to copy, then press the Ctrl + C key combination -> Select the destination position -> Press the Ctrl + V key combination. The copied data will be displayed on the Excel screen.

Auto populate data

The condition to automatically populate data in Excel is that you have to manually enter the correct formulas in the cells of the table (you only need to enter the data of that formula once). Then click and hold the bottom right corner of that square and drag down or drag horizontally, depending on the cells you want to fill in. The data on those cells will automatically populate.

Quickly display formulas

In a spreadsheet, there are many formulas that you want to check. To see all the formulas quickly on that spreadsheet, just press the Ctrl key with the ~ key.

Search in Excel

When searching or replacing information in an Excel sheet, you can follow the steps below:

Step 1: Press the Ctrl + F key combination to open the Find & Replace dialog box -> select Options -> Format.

Step 2: Select the format you want to search in Find Format.

Step 3: Formatted cell successfully.

Connect data with the ” & ” key

Connecting 2 data together will be done in the following sequence. Select the cell in the worksheet where you want to place the combined data. Then, enter the equal sign and select the first cell you want to combine -> enter & and use the double quotes space and then add the double quotes and & . Then select the second cell you want to combine -> Press Enter. Example formula: =A3&” “&B3.

Sort in Excel

This is an Excel trick that you need to know, which is the Sorting trick in Excel. Specifically, this sorting is performed as follows:

Example: There is a data table including: order code, customer, payment date, amount, payment method. The Excel sorting steps will be as follows:

Step 1: Select the entire data in the table: Select cell A2 -> Then, select to the last row by pressing the Ctrl + Shift + down arrow key combination -> Press the Ctrl + Shift + right arrow key combination to select the last column.

Step 2: From the toolbar, select Data => Select Sort. At this step, if you want to sort data tags, you will fill in the Sort by, Sort on, and Order sections.

Sort by: Sort the sequence of the vertical column “Payment date”
Sort on: Values means select each value in the cell
Order: Sort in ascending order, from oldest to newest or increase in value over time.

Step 3: Click OK, the data will be displayed correctly.

Align in Excel

To align in Excel, you need to ensure that the unit of measure is Centimeters. If it is not in cm, you need to perform the following operations to change it: Open Excel -> Click on the File tab -> Select More -> Select Options -> Select Advanced. In Rule units, click on Centimeters -> Click OK.

After successfully changing the unit, you can perform the following steps to align:

Step 1: In the worksheet, you select Page Layout, then select Margins -> select Custom Margins.

Step 2: You proceed to align as desired, then click OK.

Select a data range in a worksheet

If you only want to print a range in the data of an Excel file but don’t know how to do it, this is an Excel trick that helps you solve this problem. Perform the following steps:

Step 1: Select the data range you want to print.

Step 2: Once you have selected the range to print, you will set the print range as follows: On the toolbar, click on Page layout -> Select Print Area -> Click Set Print Area. After setting the print range, you can proceed to print as usual, and you can also adjust it like printing other documents before if needed.

Set the cursor direction when pressing the Enter key

Enter data in the cell of the spreadsheet and when you press Enter, the cursor will move to other cells. If you want the cursor to move to the cell to the left or right for convenient data entry in the row format, perform the following steps:

Step 1: In Excel, select the File tab, then select Options.

Step 2: Select Advanced, then select After Pressing Enter, move Selection > in the Direction section to select the position of the cursor, then press OK.

Hide the data in a cell in Excel

This is one of the most important Excel tricks that you shouldn’t overlook, which is hiding the data of a cell in the spreadsheet. Perform the following sequence:

First, you select the cell or range of data that you want to hide, then select the Home tab -> select Font -> click on Open Format Cells.

Next, go to the Number tab -> click Custom -> Select the ;;; option -> click OK. In addition to this method, you can also use the Ctrl + 1 key combination -> select Custom -> Enter ;;; -> Click OK.

Fixed columns in Excel

Fixing the first column

To fix the first column, you perform the following steps: Select any cell in the Excel table, then select View -> select Freeze Panes -> click on Freeze First Column.

Fixing multiple columns

Step 1: Select the column range you want to fix, then select the first cell of the column next to the column range you want to fix.

Step 2: Select the View tab -> select Freeze Panes -> press Freeze Panes to fix the columns.

Enter data with a leading 0

In Excel, if you enter data with a leading “0”, for example: 002, it will automatically remove the leading zeros. If you want to fix this issue, simply press the ‘key before entering or right-click on the cell, the data range, choose Format cell-custom-write 8 zeros.

Summary

Those are the best Excel tricks for office workers that we have collected. Hopefully, the information in this article is truly useful and helps you apply it efficiently in your work and study. Stay tuned to FPT’s articles to learn more tips and tricks about office computers!