1. Managing data with tables
Welcome back. In this video, we're continuing with the Kings County House Prices dataset. If you're following along, ensure you've opened your local copy of Excel and have located the managing table starter file within the workbooks folder and the demo subfolder. Today, we'll transform our data into a table, making our dataset not just easier to manage but also more dynamic and intuitive to work with.
To start, you can select all the data you want to include in your Table. You can do this manually or simply press CTRL plus A on both Windows and Mac to select everything from top to bottom.
Once selected, head over to the Insert tab and click on Table. Our data already has headers, so make sure the My Table has headers option is checked, then click OK. With our data now in a table format, let's explore structured references. Unlike traditional cell references, structured references allow you to use column names, making formulas easier to understand and maintain.
They're also dynamic, adjusting automatically as your table changes. To demonstrate, let's adjust our formula to utilize structured references. First, let's replace our original column that used basic cell references. Right-click the column you want to remove, Select delete, then right-click the square foot living column and choose insert for a new column to the left.
When we rewrite our formula for bedrooms plus bathrooms, notice how Excel uses the column names marked with an at sign for a clearer and more intuitive reference. Now, let's filter our data to show houses with three or more bedrooms. Click the drop-down in the bedroom column header, deselect 0 to 2, and press OK.
If you look at the status bar, you'll see the count of selected rows, giving us the number of properties meeting our criteria. Next, we'll apply a custom sort to organize our data further. Go to sort and filter, select custom sort, and let's sort by date from newest to oldest, then by price from highest to lowest.
Tables also excel in presentation. Our price column, for instance, would be more readable as a currency. Highlight the column, navigate to the number section under the Home tab, and switch the format to Currency. Let's also remove the decimal places for a cleaner look by clicking the Decrease Decimal button twice.
To wrap up, clearing any applied filter is straightforward. Just hit the Clear Filter from the drop-down where the filter is applied. It's that easy. And there you have it. Your data is not only more organized but also far more accessible and manageable. Experiment with these features and see how they can streamline your workflow.
2. Let's practice!