Office hacks: Join forces and use Office components to display the latest data

Normally, we often need to display some latest data, such as real-time sales data, real-time display of employee billing performance for motivating other employees, etc.. Although you can directly display the sales data in Excel table to the big screen, this way seems rudimentary. Using Excel 2019 combined with PowerPoint 2019, we can display it more visually and vividly. The following is an example to show the sales performance of employees of an insurance company on a large screen in real time (Figure 1).


For real-time data, you can extract it with the help of Excel’s combination function. First open the employee performance sales statistics report, follow the prompts to enter the staff real-time billing data. Position to G1, enter the formula “=MAX (Sheet1!A:A)” (the formula indicates that the cell in the cell shows the maximum value of column A of Table 1, that is, the date of the latest input); in H2, enter the formula “=VLOOKUP($G$1,$A$1:$E $26, COLUMN (B2), FALSE)”, and then drag the formula to the right to fill the K1, so that you can display the latest sales data at G1:K1 (Figure 2).


Formula explanation.

Here use the VLOOKUP function to extract the latest sales data, to find the value of G1 display time shall prevail ($G$1 said absolute reference), to find the range of $A$1:$E$26 (said absolute reference A1:E26 region, that is, the sales data input region), to find the number of columns is COLUMN (B2), here use the COLUMN function of the relative reference to the column The right pull will find the C, D, E columns in turn, FALSE indicates that the exact search. Thus, as long as we enter new billing data in the A1:E26 region, it will automatically refer to G1:K1.

New worksheet 2, the table 1 of A1: E1 content copied to G13: K13, then positioned to G14 (that is, about the middle of the document can be) and enter the formula “= Sheet1!G1”, fill to the right to K14, that is, in the location of worksheet 2 reference table 1 G1: K1 values. Position to G18 and enter the formula “=” Congratulations “&CHAR(10)&H14&I14&”in” &TEXT(G14, “yyyy year mm month dd day”)&CHAR(10)&”successful billing “&J14&CHAR(10)&”the amount is:”&K14&”yuan” “, so that in G18 will display the contents of the billing happy. As prompted to fill the G18 cell background color to red, set the appropriate font and select “centered display” (Figure 3).


Formula explanation.

Here the main use of the “&” character will be specified in the cell connected (note that the Chinese characters to be marked with a half-angle double quotation marks), which “CHAR (10)” said line feed, “TEXT (G14,” yyyy year mm month dd day”)” means that the use of Text function to display the date as a form of year month day. Because we use the “&” connected to the cell can no longer be set individually to the characters, you can choose to connect the characters according to the actual needs, such as “congratulations” placed in G17 alone, and then set it to other fonts, so that the actual display is more eye-catching. This is more eye-catching when actually displayed.

In order to better display the data, here you can also use the “link image function” to convert the above link text to images for subsequent beautification such as three-dimensional processing. Select G18 and copy, positioned to I19 (not and the original data in a row, to facilitate subsequent adjustment of the picture), select “Start → Paste → selective paste → linked pictures”, adjust the size of I19, so that the picture just fill the entire cell. Click the “picture tool format → format → three-dimensional format”, follow the prompts to set the appropriate three-dimensional parameters. Of course, you can also set its light, shadows, softening and other effects, so that the display effect better (Figure 4).


Select rows 13 and 14 and right click on them, select “Hide” to hide the original data. Click “View” in the menu bar and remove the check box of “Gridlines”, so that you can display the picture of the happy report on a blank page for subsequent display in the PPT.

After completing the above operations, start the PPT for data presentation. First, create a new blank layout slide, click “Start → Paste → Selective Paste → Paste Link → Microsoft Excel Worksheet Object”, the I19 cell to link the way to paste into the slide (Figure 5).


Select the picture and click “Animation→Add Animation”, add a “flip-flop” entry animation for it, set “Start” to “and the previous animation at the same time”, the duration of 3 seconds. Switch to “Animation Pane→Effect Options→Timing”, set “Repeat” to “until the end of the slide” (Figure 6).


Click “Slide Show→Set Up Slide Show”, select “Speaker Screening (Full Screen)” as the projection type, check “Loop, press Esc to terminate” as the projection option, and select from 1 to 1. Then prepare a picture of the wedding announcement template and place it at the bottom. In this way, a slide show of the happy news is completed (Figure 7).


Finally, the PPT will be moved to the extended large screen for full-screen projection, and the large screen will cycle through the happy data. As the picture here is linked to the data in Excel, when we enter new data in Excel, the slide will also synchronize the latest data (if not synchronized, right-click the picture and select “Update Link”). This way, the display will be more effective (Figure 8).


Leave a Comment