When there is a lot of data in a table, it is troublesome to work on a table in the traditional way. With the help of some advanced operation tips, you can make the troublesome operation process into a simple and interesting action, thus improving the data processing efficiency to some extent.
1. Monitor cells with the monitor window
When dealing with a long worksheet, it is not easy to determine how the change in the value of a cell affects other cells. The situation is even more complicated if the calculation of the cell depends on the value of an invisible cell somewhere on the screen, we can’t pull up and down the scrolling table after each change in the value of the cell to see the change in other values, right? If you rely on the monitoring window, it can help us efficiently observe the changes in the value of dependent cells.
First, open a workbook in Excel, select the cells to monitor, and then under the “Formula” tab, select “Monitor Window”. Then, the “Monitor Window” dialog box will automatically open. Click “Add Monitor” (Figure 1).
In the “Add Watchpoint” dialog box that then appears, click the “Add” button to add the cells to be monitored (Figure 2).
Then, when the table data is changed, the Watch Window will show the changes in the values of the dependent cells. The Watch Window will always be floating on the worksheet and we can also resize it. Even, we can watch the data changes in other cells on the sheet (Figure 3).
2. Concise representation of large numbers
If we only need to view the trend of the data in general, for example, what is the average salary of each department in a company in 2018 and what is the total salary expenditure in 2019. If we put very long numbers in the cell, it looks rather cluttered (Figure 4). Therefore, formatting them and displaying them in a simplified way, for example, with a “k” for thousand, makes it look concise.
To achieve this effect, simply select the range of cell areas, and then right-click the selected area and select “Set Cell Format”. In the Set Cell Format window, select the number category as “Custom”, and then enter “####, “k”” in the Type dialog box, click “OK” (Figure 5).
Then, we see the relatively straightforward “rounding” result (Figure 6).
It is important to note here that although we see simplified rounded values in the table after formatting, for example, the value 22786.34 looks like 23K, this only changes the appearance of the value display, while its actual value remains the same. Therefore, the values in these formats will not have any problems if they are involved in the calculation.
Tip: We can also format the axes displayed in the chart in the same way. Just select the axes to be formatted and perform the above procedure. 3.
3. Apply the correct table style to the selected data
To quickly beautify the table, we can apply some common table styles to the selected table data. First, select the data to which you want to add table formatting, and then click “Style→Apply Table Format” under the “Start” tab in turn. After selecting the desired table type, select the “table contains the title”, and then click “OK” to apply the table style to the Excel data (Figure 7).
However, in the above application results, we can see that filters are added to the top of each column, and if you wish to remove them and keep the selected range, click “Convert to Region”. Excel will then ask “Do you want to convert the table to a normal region?” , click “Yes”. Filters are then automatically removed (Figure 8).
4. Create custom lists from existing data
In an Excel sheet, it is always easier to select values from a drop-down list than to enter them manually. By creating custom data entry lists in Excel, you can save time on input. To do this, first select the cell location where you want to generate the drop-down list in the column containing the list you want to generate. In the “Data” tab, click “Data Validation → Data Validation”, open the “Data Validation” dialog box, from the “Allow “drop-down list, select “Sequence”. In the Source box, specify the range that contains the originally typed values, and then click OK (Figure 9).
After that, click on the cell to which data validation has been added, a drop-down arrow will be displayed, and clicking on it will display the defined list from which you can select the desired item to complete the entry.