Figure E To make the most of the Table arrangement, use a structured reference in the COUNTIF() functions.Specifically, replace the absolute range reference with the name of the new Table, as shown in Figure F.Because the reference is absolute, the function won't update automatically to include new data added each day.You probably know that you can insert a row or column into a range to take advantage of automatic referencing. As you can see, the functions update, but the new row introduces new problems: Although I didn't show it using the example data set, the same troublesome potential exists when inserting a column. Don't store anything else on the sheet with your data set, and inserting suddenly becomes an easy solution.A good design will accommodate most growth and changes, but often, you find yourself working with a data set that doesn't conform well to what you need to do.Fortunately, with a little know-how, you can get what you want.Most of the people create a VBA program to import and also manipulate the data.
Because your data is in a table, your table is link to your source of data. With Power Query, when you import for the first time a file (csv, txt, Excel, …), the program records for you all the step you want.You could even create a macro that inserts new columns and rows for your users.Depending on the expertise level of your users, you might consider converting the data set to a Table object (supported by Excel 2007 and later).When you insert columns and rows, the references will update automatically.To convert a data set into a Table, do the following: With the new Table object in place, you can insert a new row quickly and easily.If you're working with a stable range where the dimensions don't increase, this solution works fine.However, as soon as you add a new row or column, you run into trouble because the COUNTIF() functions won't update automatically to include the new data.As you can see in Figure B, the counts remain the same even after adding a new row and column.The absolute reference in the range reference is necessary in this case.Whatever the manipulate you need to do on your data, the final result, with Power Query, will be always inside a table. When your data are in a table, this means you have create a connection between your source and your Excel workbook.From CSV In the dialog box, you select your file and when you validate. It’s in this interface that you will manipulate your data.