Realize dynamic data table with Excel

Every year at the end of the year, opening an Excel icon can be very annoying. Excel chart data most people feel that there is a lot of dense data. Although the contents of the histograms appear clear and clear, it does not always seem intuitive. In particular, when there is a large amount of data, the page-by-page flipping is even more troublesome. Is it not convenient if you can modify the Excel settings so that the data can be moved by itself in a table? Today we will make Excel charts become dynamic data tables.

Open Excel and switch the tab to the development tool on the page to be created as a dynamic data table (If there is no development tool, click Excel File - Options - Customize Ribbon, right-click the development tool), select "Insert "The scroll bar in the form control.

Right-click on the scroll bar and select Set Control Format.

Switch the tab to the "Control" item to modify the cell link. For example, we have selected the blank cell E9. (Click the small icon after the cell link can directly select the cell)

Press Ctrl+F3 to open the Name Manager, which is a bit more complicated.

The window that pops up is a new custom name. For example, if the first name is a date, enter =OFFSET (Sheet1!$A$1,Sheet1!$E$9,,5).

The second example is called data, and the same input =OFFSET(Sheet1!$B$1,Sheet1!$E$9,,5).

It is actually the location of these two names. What this means is that the OFFSET function's row offset is specified by the E9 cell, and the E6 cell is controlled by the scroll bar control so that each time the scroll bar is clicked, the OFFSET function's row offset changes.

Now start the chart, select the "Insert" tab in the Excel interface, find the small icon of the line chart (near the recommended chart), and click to select a line chart.

Right-click on the newly created chart data and select "Select data".

Edit the left-hand column items (series) and horizontal (classification) axis labels in the selected data source, and modify the custom names (date, date) corresponding to the previous one.

Finally, according to the actual needs, modify the height of the chart, the height of the cell, and complement the background of the beautification work. In the end, we can rely on the scroll bar to move the icon data and achieve dynamic data browsing. Is it more interesting than this?