Office hacks: data reporting more humane quickly generate Excel specific format text

In Excel, many data are arranged in a tabular manner, but sometimes in order to make the data more readable, you need to re-organize the text in a certain format. For example, in order to make it easier for leaders to view the daily sales reporting data, we organize the text in cell F5 in the style of Figure 1 (Figure 1).


Figure 1 Example text

As you can see, the core content of the above text is the data in column A:D (each cell corresponds to a line of content), and then add the words “Daily Report” and “Reported by:”, so we can use the text linker “&” to connect the data content.

In order to facilitate the display of text, we first create a new table for “reporting”. Then locate to the new table cell A2, enter the formula “=Sheet1!A2+1” (which means that the original date plus 1, that is, today’s report is yesterday’s statistics), locate to cell B2, enter the formula “=” Daily Report “&CHAR(10)& “Report date:” & TEXT (Sheet1!$A2+1, “yyyy year mm month dd day”)&CHAR(10)&Sheet1!$B$1&”:” &ROUND(Sheet1!B2/10000,2)&”million,” &CHAR(10)&”which:” &CHAR(10)&Sheet1!$C $1&”:”&ROUND(Sheet1!C2/10000,2)&”million,”&CHAR(10)&Sheet1!$D$1&”:”&ROUND(Sheet1!D2/10000,2)&”million”&CHAR(10)&”Reporting by: Sales Department Zhang San”” (Figure 2) .


Figure 2 Formula settings

Formula explanation.

“Daily Report” & CHAR(10): 8 spaces are added before “Daily Report” to center the character, then use “&” and CHAR function to connect, CHAR(10) means line feed key.

TEXT (Sheet1!$A2+1, “yyyy year mm month dd day”: use the TEXT function to convert the date format of cell A2 to the year, month, day display text, $A2 that the absolute reference to the A2 column.

Sheet1!$B$1&”:”&ROUND(Sheet1!B2/10000,2)&”million: $B$1 means the absolute reference to cell B1, that is, always show “sales subtotal”, $C$1, $D$1 reference similar. ROUND for rounding, display two decimal.

In cell A2, B2 after displaying the text data referenced, we can set the background color, font, font size and other content in accordance with actual needs. Then drop down the fill formula, so that all the cells behind will automatically use the format set to generate the specified text, which looks very intuitive (Figure 3).


Figure 3 Text display

Well, now we just need to copy this text or take a screenshot and send it to the leader. By way of example, we can also make text of other data, such as employee attendance information, etc. (Figure 4).


Figure 4 Attendance text data

Leave a Comment