Office Hacks: Doing the table in Excel smartly

When we contain a large amount of data to filter the table, if you want to filter the data in the form of a more intuitive and eye-catching table with the source data displayed at the same time (Figure 1), so as to facilitate comparison, processing, demonstration, how should we achieve it? In fact, as long as the clever use of filtering functions, images, combination boxes and other functions, it is very easy to create this effect.



The operations covered in this article can be completed in Excel 2013 and above. The operations and screenshots in this article are based on the Excel 2019 version as an example, and other versions may have different operating interfaces.

First create a drop-down menu. In the blank column to continuously enter the required menu content (that is, the example of the province), select the “Development Tools” tab, click “Insert → Form Controls → Combo Box”, in the blank space of the worksheet to draw a combo box, right-click the combo box, select “Set control format”, in the pop-up window of the “Control” tab, “Data Source Area”, select the input cells of consecutive provinces, such as $F$1:$F$4. “So that when you click the drop-down menu in the combo box, the value of cell G1 will change (Figure 2).


Then, build a “Name”. Switch to the “Formulas” tab, click on “Name Manager”, click on the “New” button in the pop-up window, enter “Data” in the new pop-up window at ” Name” in the new pop-up window, enter “Data” in the “Reference Location”, enter “=OFFSET(Sheet1!$B$2, (Sheet1!$G$1-1)* 5,0,5,3)”. In this way, the data needed for the table is constructed and this data will change according to the drop-down menu (Figure 3).


Next, switch to the “Insert” tab and insert any picture. Adjust the size of the picture, select the picture, enter “= data” in the edit field, the picture has been displayed as a table. Right-click this picture, it can be filled with color, lines, three-dimensional style settings (Figure 4).


Make another table header, design its style according to the previous picture and take a screenshot, adjust the size of the screenshot. In cell G2, enter “=OFFSET($F$1,$G$1-1,0,1,1)”, so that the selected menu name is obtained in cell G2; in cell G3, enter “=G2&” Sales and profit statistics by month” “; insert a rectangle, select it, enter “= G3” in the edit field, set the desired style of this rectangle. In this way, the table title of the table is designed. Finally, right-click the drop-down menu, place it on the top level, the drop-down menu, rectangle and the previous two pictures of the position of the adjustment and the combination can be (Figure 5).


Leave a Comment