Office hacks: A little bit of Excel practical tips to break

In the daily processing of Excel tables, some needs may seem simple, but the realization of the moment may make people do not know how to start. For example, to create multiple rows of text in a cell, or for the data of non-contiguous cells to create charts and so on. In fact, if you know the trick, the operation to achieve these needs is very simple.

Tip.

All tips are operated through in Excel 2019.

1. No formulas Quick ad hoc calculations

If I temporarily need to refer to the calculated values of several non-contiguous cells in a table (such as the average, maximum, minimum or sum, count, etc.), do I have to construct a formula or use a calculator program to do the math? No!

We simply press the Ctrl key and select the cells involved in the calculation. Next, the desired value will be visible on the status bar at the bottom of the Excel worksheet. If the desired value does not appear, you can right-click on the status bar, and then select the desired item from the pop-up menu, then the status bar will appear in the calculated value of the selected item (Figure 1).

2015A-JQ-1

Of course, for a large amount of data aggregation and calculation, it is more convenient and efficient to use functions and formulas.

2. create charts from non-adjacent cells

To create a chart for multiple cells of non-contiguous data, simply use the Ctrl key to first select the non-contiguous cells of data, and then select “Insert” chart can be created (Figure 2).

2015A-JQ-3

3. Create templates for frequently used charts

Creating templates for commonly used charts can greatly simplify the operational steps for creating similar charts. The specific method is as follows.

First select the desired data in the table and insert the chart in the usual way. Next, format the chart as needed, such as adding or removing grid lines, data labels, trend lines, and other content. After the chart has been formatted, select the chart and right-click on it, then select “Save as Template” to save the chart as a template file with a “.crtx” extension (Figure 3). The default path to save the template is “C:UsersUsernameAppDataRoamingMicrosoftTemplatesCharts” (note that Username should be replaced with the current user name), and the path can be customized. The path can also be customized.

2015A-JQ-4

Then, if you need that style of chart again, just select the latest data, go to the “Insert→Chart” section, click the small arrow in the lower right corner of the group and select the “All Charts” tab, then click “Template ” and select the desired template to create a custom chart for the latest selected data.

Leave a Comment