Exercise: Excel 101
“I’ll show these people what you don’t want them to see. A world without rules and controls, without borders or boundaries. A world where anything is possible.” - Neo (The Matrix, 1999)
If you haven't seen The Matrix film, Neo's quote here is when he first awakens from the machine world and starts teaching the other humans the basics of how to use Excel (Excel 101). And I agree with him: with Excel anything is possible.
On a more serious note, welcome to our first exercise! You'll see exercises sprinkled throughout this course, and I highly recommend you do them - exercises are the key to solidifying your understanding of the concepts. And besides, doing is the most fun part of learning!
Of course, if you have any questions or get stuck, hop on over to the #excel channel in ZTM Discord server (invite link available in the "Join The Community" lesson) where I'll be hanging out to answer any questions you may have.
*All files referenced in exercises are available to download directly below.
Exercise: Excel 101
- Download and open the "Customer Reviews.xlsx" file directly below (at the bottom of this page).
- Create a new worksheet - positioned to the left of the other worksheet in the file - named "08-12-2021".
- Download and open the "Customer Reviews 08-12-2021.xlsx" file below.
- Copy and paste the data in the "Customer Reviews 08-12-2021.xlsx" file into cell A1 of the "Customer Reviews.xlsx" file.
- Delete the "ProductID" column in the data.
- Unmerge the two cells composing the "CustomerName" column header, and then add "Customer First Name" and "Customer Last Name" headers for the two columns.
- Cut the "Customer First Name" and "Customer Last Name" columns, and insert them to the left of the "ReviewStars" column.
- Edit the remaining column headers to have spaces between the words in the names (i.e, "ProductName" becomes "Product Name").
- Insert a new column, to the left of the other columns, and give it a column header of "Review Number".
- Using Excel's autofill feature, populate this column with a series of numbers from one to the number of reviews in the dataset.
- Re-format the "Purchase Date" and "Review Date" columns to the MM/DD/YYYY date format.
- Re-format the "Review Stars" column to display zero digits after the decimal place.
- Using Excel's find and replace feature, replace all instances of the word "ukelele" on the worksheet with "ukulele".
- Change the font of all the cells in the range to "Segoe UI", size 10.
- Give the column headers a dark background fill color from one of Excel's built-in "theme" colors - your choice!
- Change the font color of the column headers to white.
- Bold the column headers.
- Auto-fit the widths of the columns to adjust to the width of the data they contain.
- Freeze the top row of the data (the column headers) so that it remains visible no matter how far you scroll down through the data.
- Save the file AS "Customer Reviews - Formatted.xlsx".
Solution: your solution should look very similar to the data on the "08-11-2021" worksheet of the "Customer Reviews.xlsx" file. Is it the same? Yes? Great job! No? Hop over to the #excel channel in the ZTM Discord server and I'll help you figure out what went wrong.