Managing data
1. Managing data
In this video, we'll focus on managing and formatting data to make it more informative and visually appealing. If you're ready to dive in, ensure you've opened the formatting data starter file found under the workbooks folder in the demo subfolder. Let's enhance our data set. Named ranges are a game changer in Excel, simplifying data reference in formulas outside of tables. Let's start by naming our price column. Select all cells in the column, excluding the header, and head over to the formulas menu. Click on define name and name this range house underscore prices. Repeat the process for the square foot underscore living column. Named ranges like these make future calculations more straightforward and intuitive. Now, if we click on the name manager, a new window will appear that shows the ranges we defined. Let's close this window. With our named ranges set, we can use this reference in a new column. In column J, add a new header titled Price per square foot living. By typing a house into cell J2, Excel auto-suggests our named range of house prices. Select it, then divide it by our square foot living range. This formula will then apply across all rows, demonstrating the power of named ranges for efficient data manipulation. To explore subtotals, let's duplicate our worksheet, right-click the tab, choose, move, or copy, tick create a copy, and confirm. Subtotals offer a structured view of data based on specific criteria, such as flaws in our case. Our price per square foot living column isn't required, so we can delete this column by right-clicking and selecting delete. Before adding subtotals, ensure your data is sorted appropriately. Sort the floor column from smallest to largest. Now, we can navigate to the data menu, click on the outline, and select subtotal. We now have multiple options for creating subtotals. Our goal is to see the average house price based on the number of floors. Therefore, we'll update each change in the dropdown column to Flaws and use the Average function. We'll then apply this to our Price column. To quickly see it grouped by Flaws, we can select two from the 1, 2, and 3 to the left of our row numbers. Enhancing data integrity is crucial for shared workbooks, and data validation is the perfect tool for this. To implement it, select the entire Price column, navigate to the Data menu, and click on Data Validation. In the settings tab, set the allow dropdown to the whole number and the data dropdown to greater than, then enter 0 in the minimum field. This ensures that only positive integers are accepted, preventing incorrect data entries. Next, switch to the input message tab and title it entry requirement. In the message box type, please enter whole numbers greater than zero only. This will display a helpful prompt whenever someone selects a cell within the price column. Lastly, under the error alert tab, choose an appropriate icon and type invalid entry. The title with a message saying only whole numbers greater than zero are allowed. Click OK to activate your data validations. Now, we can try to enter an incorrect value at the bottom of our dataset. Excel will display a custom error alert guiding users toward valid inputs and maintaining the dataset's integrity and structure. Next, let's refine our date format for better readability. Select the date column, navigate to more number formats, and choose a custom date format. Let's say that we want our date shown as two digits, The month name with three characters, and the year in full; we can set it to DD-MMM-YYYY and click okay. Finally, we'll finish by highlighting the top 100-priced houses. In our data set, we'll use conditional formatting. From the home menu, select the top and bottom rules and then the top 10 items, adjusting the criteria to 100. We can then set our formatting type and rules for our formatting. We'll ensure green fill with green text is selected. This visual cue makes significant data stand out at a glance. Experiment with these features and apply them to your data sets. Ready for a challenge? Try some exercises to put your new skills to the test.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.