More functions, better preparation
1. More functions, better preparation
Let’s continue working on our car dealership data. As we have imported an external file, Excel might give us a security warning when we open the file. We can click Enable Content and carry on working. Let's apply some quick formatting to make the data easier to read. Let's make the headers bold by clicking on the B in the Font section of the Home menu. Then let’s fill the cells gold by choosing the gold color from the menu that appears when clicking on the paint can icon. Then add filters. We can simultaneously widen all the columns to make the headers and values visible. Perfect. Let’s combine two columns now. Transmission and Drivetrain contain text data types. They also have two and three different values, respectively. We could get more insight if these two variables made one categorical variable when analyzing the data. Before joining, let’s check the values in Drivetrain. It has three different variables, along with blanks. Let’s replace the blanks with “NA”. Select the column, click Find & Select, and then Replace. In the “Find What” section, we will leave it blank, and then in the Replace with; we will type NA. Click Replace All, and a message will indicate how many cells were changed. We can check that the values have been added using the filter menu. Now, we will add a new column next to the drivetrain and name it Transmission & Drivetrain. We will use the CONCAT function here, and the first variable will be the Transmission value in D2. As the function adds no delimiters, we will add them manually. Let's use a dash for this column. Whenever we add text or characters into a formula, we have to enter it using double quotations. So after the first variable, we will type out a double quote, em dash, double quote, and then a comma. This is our second attribute in the formula. The final part is the Drivetrain value, which is in cell L2. Nice. Let’s populate the column. Now we have a category with more variables to split our data by. Time for some date and time functions! In this sheet, a date column at the end shows the date the cars were listed as available. It would be even better to indicate the day of the week. We could then use this to see which day of the week had more cars listed. In T1, we will add the header “Weekday”. In the next cell, we will use the formula WEEKDAY and the date in the Listing Date column to start creating the values for the column. We will use Monday as the start of the week, so we will enter two for the second variable of the formula. Let’s populate the whole column. That’s looking good. One important tip when preparing and changing the data in Excel is the ability to undo a step. Let’s say we made an error with the formula we entered for the whole column. We can click on this backward curled arrow to undo our last step. We can keep clicking it if we want to go back to further steps. Just as we can undo, we can also redo. Next to the undo button, we can now see a forward curly arrow. This will redo what we just undid. When we are done preparing this file, we will have to share it with our colleagues. So let’s start protecting the content and layout of the file to ensure its integrity. In Extra Details, let's go to Review in the top menu ribbon and click on Protect Sheet. We will wait to add a password, as we might need to carry out some further editing later on. But we will choose some useful options when viewing the sheet from now. Let’s select the first six options and then click OK. We can now see that in the Review menu, the Protect Sheet button has changed to Unprotect Sheet. Excellent, time to give it a go yourself.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.