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 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 tables. Let's start by naming our price column. Select all the cells in the column, excluding the header, and head over to the formulas menu. Click on define name and name this range house_prices. Repeat the process for the square foot_living column. Named ranges like these make future calculations more straightforward. 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 house into cell J2, Excel auto-suggests our name range of house prices. Select it, then divide 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 are for a structured view of data based on specific criteria, such as floors 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 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 floors and use the average function. We're going to apply this to our price column. To quickly see it grouped by floors, we can select two from the one, two and three 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 allow drop down to whole number and the data drop down to greater than, then enter zero in the minimum field. This ensures that only positive integers are accepted, preventing incorrect data entries. Next, switch to 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. This title with a message saying only whole numbers greater than zero are allowed. Click okay to activate your data validations. Now we can try to enter an incorrect value at the bottom of our data set. Excel will display a custom error alert guiding users toward valid inputs and maintaining the data set'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 we want our date shown as two digits, the month name with three characters and 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 queue 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.