Aspiring VIRTUAL ASSISTANT? Here's Google Sheet Customer Order Tracker tutorial to Practice #3

Aspiring VIRTUAL ASSISTANT? Here's Google Sheet Customer Order Tracker tutorial to Practice #3 VA PRACTICE BOOK 03 GOOGLE SHEETS PRACTICE Customer Order Tracker Track customer orders and monitor their status. PAGE 1: CLIENT REQUEST Read the email carefully. From: rachel.lee@sunrisehomeph.com To: you@yourname.com Subject: Create a Customer Order Tracker Hi there, I hope you're doing well! I'm starting to receive more orders for our online store, and I need a simple and organized tracker to monitor all customer orders. Please create a Google Sheet with the following columns: Order Date, Order ID, Customer Name, Product / Item, Quantity, Unit Price, Total Amount (with formula), Status (Pending, Processing, Shipped, Completed). Please use the sample data I will provide in the next pages. I also need to see the total sales and how many orders are still pending. Let me know if you have any questions. Thank you! Rachel Lee Owner, Sunrise Home PH Home Essentials for a Better Everyday. Let's build your Google Sheet step by step! YOUR GOAL Create a clean and organized Customer Order Tracker in Google Sheets. Use formulas to calculate total amount, total sales, and count pending orders. Apply proper formatting and highlight order statuses. PRACTICE LEARN GROW ANNE SHAOSHAO Page 1 of 10 VA PRACTICE BOOK 03 GOOGLE SHEETS PRACTICE Customer Order Tracker Track customer orders and monitor their status. STEP 1: CREATE THE SHEET Let's start by creating a new Google Sheet. 1. Go to Google Drive. 2. Click "+ New". 3. Choose Google Sheets. 4. A new sheet will open. 5. Name your file: Customer Order Tracker. Step by step today, real skills tomorrow. PRACTICE LEARN GROW ANNE SHAOSHAO Page 2 of 10 VA PRACTICE BOOK 03 GOOGLE SHEETS PRACTICE Customer Order Tracker Track customer orders and monitor their status. STEP 2: ADD THE HEADERS Set up the column headers for your Customer Order Tracker. 1. In your Google Sheet, click on cell A1. 2. Type the column titles exactly as shown. 3. After typing all headers, select row 1 (A1:I1). 4. Format the header row. 5. Your headers are now ready! HEADERS TO ADD: Order Date, Order ID, Customer Name, Product / Item, Quantity, Unit Price, Total Amount, Status, Notes. PRO TIPS: Keep the headers short and clear. Use consistent naming. You can adjust the column width. Use a light color for the header row. PRACTICE LEARN GROW ANNE SHAOSHAO Page 3 of 10 VA PRACTICE BOOK 03 GOOGLE SHEETS PRACTICE Customer Order Tracker Track customer orders and monitor their status. STEP 3: ENTER CUSTOMER ORDERS Input the sample customer orders into your sheet. 1. Look at the sample data on the right. 2. Go back to your Google Sheet. 3. Type the information exactly as shown. 4. Check for accuracy. 5. Your data should now look like the sample. REMEMBER: Enter the data neatly and correctly. Keep the same format as the sample. You can add more rows if needed. Accuracy is important! PRACTICE LEARN GROW ANNE SHAOSHAO Page 4 of 10 VA PRACTICE BOOK 03 GOOGLE SHEETS PRACTICE Customer Order Tracker Track customer orders and monitor their status. STEP 4: CALCULATE TOTAL AMOUNT Use a formula to calculate the total amount for each order. 1. Go to cell G2 (Total Amount). 2. Type the formula: =E2*F2. 3. Press Enter. 4. Copy the formula to the rows below. 5. Check the results. THINGS TO REMEMBER: Use =E2*F2 (Quantity x Unit Price). Press Enter after typing the formula. Drag the fill handle to copy the formula down. Make sure all total amounts look correct. PRACTICE LEARN GROW ANNE SHAOSHAO Page 5 of 10 VA PRACTICE BOOK 03 GOOGLE SHEETS PRACTICE Customer Order Tracker Track customer orders and monitor their status. STEP 5: CALCULATE TOTAL SALES Use a formula to add all the total amounts and find the total sales. 1. Scroll to a blank row below your data. 2. In column F (Total Amount), click on the cell below the last order. 3. Type the formula: =SUM(F2:F11). 4. Press Enter. 5. Label the cell. TIPS FOR SUCCESS: Use the SUM function to add numbers quickly. Always check the range (F2:F11) to make sure it includes all your data. Format the total sales as currency. You can also add more summary formulas later. PRACTICE LEARN GROW ANNE SHAOSHAO Page 6 of 10 VA PRACTICE BOOK 03 GOOGLE SHEETS PRACTICE Customer Order Tracker Track customer orders and monitor their status. STEP 6: COUNT PENDING ORDERS Use the COUNTIF function to count the number of orders with "Pending" status. 1. Choose a cell for your count. 2. Type the formula: =COUNTIF(H2:H11,"Pending"). 3. Press Enter. 4. Label the cell. 5. You can also count other statuses. TRY THESE FORMULAS TOO: Count Completed Orders =COUNTIF(H2:H11,"Completed"), Count Processing Orders =COUNTIF(H2:H11,"Processing"), Count Shipped Orders =COUNTIF(H2:H11,"Shipped"). PRACTICE LEARN GROW ANNE SHAOSHAO Page 7 of 10 VA PRACTICE BOOK 03 GOOGLE SHEETS PRACTICE Customer Order Tracker Track customer orders and monitor their status. STEP 7: HIGHLIGHT STATUSES Use Conditional Formatting to highlight the Status column with different colors. 1. Select the Status column. 2. Go to Conditional Formatting. 3. Add a rule for Pending. 4. Repeat for other statuses. 5. Your Status column will now be color-coded! TIPS: Use consistent colors for each status. Conditional formatting updates automatically when you add new data. You can edit or delete rules anytime. Color-coding helps you quickly identify which orders need attention. PRACTICE LEARN GROW ANNE SHAOSHAO Page 8 of 10 VA PRACTICE BOOK 03 GOOGLE SHEETS PRACTICE Customer Order Tracker Track customer orders and monitor their status. STEP 9: CHECK AND ORGANIZE Review your data, make sure everything is correct, and organize the sheet for a clean and professional look. 1. Check your data. 2. Look for errors. 3. Auto-fit column width. 4. Freeze the header. 5. Make it look neat. EXTRA TIPS FOR A CLEAN SHEET: Sort your data, Use filters, Add borders, Keep text alignment consistent. FINAL CHECKLIST: All data is complete and correct. Formulas are working properly. Columns are well-sized. Header row is frozen. The sheet looks clean and professional. It is ready to be submitted! PRACTICE LEARN GROW ANNE SHAOSHAO Page 9 of 10 VA PRACTICE BOOK 03 GOOGLE SHEETS PRACTICE Customer Order Tracker Track customer orders and monitor their status. STEP 10: SUBMIT THE COMPLETED TASK Your Google Sheets file is now complete! Follow the instructions below to prepare and submit it. 1. Review the instructions. 2. Rename your file. 3. Check access settings (if needed). 4. Submit your work. 5. Write a short message. WHAT YOU LEARNED: Create and organize data in Google Sheets, Use formulas (SUM, COUNTIF), Apply conditional formatting, Format and clean your sheet, Prepare and submit your work, Build a real-world VA skill. Keep Practicing! You're One Step Closer to your VA Goals! PRACTICE LEARN GROW ANNE SHAOSHAO Page 10 of 10