Microsoft Office: Excel

Entering and Formatting Data

Now that you've explored the Excel interface and learned how to navigate the ribbon, it’s time to start working with real data. In this module, you’ll build a simple employee list while learning how to enter information into cells, format your data to make it easy to read, and adjust rows and columns to keep things neat. These foundational skills will help you feel more confident working in Excel—and set the stage for everything to come. Let’s get started!

Step 1: Start a New Worksheet

You’ll be building your employee list in a new worksheet within the same workbook you used in for the Scavenger Hunt. Follow these steps:

1. Open the Excel workbook you used in Hour 1.

2. Look at the bottom of your screen—you’ll see one or more tabs labeled something like Sheet1 or the name you gave it. 

3. Click the plus (+) icon next to the last sheet tab. This adds a brand-new worksheet, usually named Sheet2.

4. Double-click on the new tab name (Sheet2) and rename it to: Employee List

5. Double-click the first tab name (Sheet1) and rename it to: Scavenger Hunt

Step 2: Enter Employee Data

1. In cell A1, Title your sheet: Employee List

2. Use merge and center to have the title span from A1-E1. 

3. Format the title to be bold. 

4. In cells A3-E3, enter the following column headers:

   • A3: Name

   • B3: Department

   • C3: Hire Date

   • D3: Salary (USD)

   • E3: Email

5. Format your headers. 

   • Make the header row bold. (Select cells A3 to E3 and click the Bold button on the Home tab.)

   • Center align the headers. (With the same cells selected, click Center Align in the Alignment group.)

   • Change the fill color to make it look like a real table. (Select cells A3 to E3 and click the fill button on the Home tab.) 

6. Now enter these employees into rows 4-6. 

   • Carlos Rivera, Marketing, 1/5/2023, 42,000, carlos@email.com

   • Amina Hassan, HR, 3/12/2022, 39,000, amina@email.com

   • Jenna Smith, Engineering, 7/5/2021, 65,000. 

Step 3: Format Your Data

1. Adjust column width. Some text might be cut off — fix it! 

6. • Hover between the column letters (like A and B) until your cursor becomes a double arrow ↔

   • Double-click to auto-fit or drag to manually resize.

2. Format the data. 

6. • Dates: Select the hire date column (C4–C6), right-click → Format Cells → Date → Pick a style you like.

   • Salary: Select the salary column (D4–D6), click the $ symbol on the Home tab to apply currency format.

3. Add a border.

6. • Highlight your whole table (A3:E6)

   • Click the Borders icon → Select All Borders to make it look more official.

Checkpoint: What should it look like? 

 At this point, your sheet should have: 

6. • A bold, centered header row

   • Adjusted columns so all text fits

   • Formatted dates and salaries

   • Borders around your table

Nice work! You just built and formatted your first professional-looking table in Excel—way to go! This may seem like a small step, but it’s actually a big one. Tables like this are used every day in the workplace to track employees, manage budgets, organize tasks, and more. Knowing how to enter clean data and make it easy to read is one of the most important Excel skills you can have. Keep practicing, and you’ll be amazed how quickly your confidence grows! As a reminder, you'll upload this employee list as part of your completed Excel workbook in your self-evaluation. 

Organizing Data with Tables

Excel tables are a game-changer for managing data efficiently. They make your data easier to read, sort, and filter—perfect for tasks like tracking project tasks, managing inventory, or analyzing customer information. In this module, you’ll learn how to transform a dataset into a table, sort it to find insights, and filter it to focus on what matters. Let’s get organized!

Step 1: Start in Your Existing Workbook

1. Open the Excel workbook you’ve been using for previous modules.

2. Add a new worksheet by clicking the plus (+) icon at the bottom of the screen.

3. Rename the tab: Task Tracker.

Step 2: Set Up Your Data

In your new Task Tracker sheet, you’ll create a dataset to practice table features. Starting in cell A1, enter the following table:

Task Name Assigned To Priority Due Date Status

Update Website Sarah Johnson High 4/15/2025 In Progress

Client Presentation Mark Lee Medium 4/20/2025 Not Started

Budget Review Aisha Khan High 4/12/2025 Completed

Team Training Carlos Rivera Low 4/25/2025 In Progress

Data Analysis Jenna Smith Medium 4/18/2025 Not Started

Instructions:

• Type the data exactly as shown, starting in cell A1 (so “Task Name” is in A1, and the first row of data begins in A2).

• Make the header row (A1:E1) bold.

• Adjust column widths if needed by double-clicking the column borders to auto-fit the content.

• Format the Due Date column (D2:D6) as Short Date (use the Format menu or right-click to select “Format Cells”).

Step 3: Convert Data to a Table

Now, let’s turn your dataset into an Excel table to unlock powerful organization tools.

Instructions:

1. Select the entire dataset (cells A1:E6).

2. Go to the Insert tab on the ribbon and click Table (or use the shortcut Ctrl + T).

3. In the dialog box, ensure “My table has headers” is checked, then click OK.

4. Notice how Excel formats your table with alternating row colors and adds filter arrows to the headers.

5. In cell G1, type the label Table Name. In cell G2, name your table: TaskTable.

   • To apply the name, select the table, go to the Table Design tab (which appears when the table is selected), and type “TaskTable” in the Table Name box on the left.

6. Test the table’s resizing feature: In cell A7, type a new task (e.g., “Send Report”, “Liam Chen”, “Low”, “4/30/2025”, “Not Started”). Watch how the table automatically expands to include this row.

Tip: Tables make data dynamic—any new rows or columns you add within the table inherit its formatting and functionality.

Step 4: Sort Your Data

Sorting helps you rearrange data to spot patterns, like finding urgent tasks or alphabetical order.

Instructions:

1. Click the filter arrow in the Priority column header (cell C1).

2. Select Sort A to Z to arrange tasks from High to Low priority.

   • Observe how the entire table reorders, keeping each row’s data together.

3. Now, sort by Due Date to see tasks in chronological order:

   • Click the filter arrow in the Due Date column (D1).

   • Select Sort Oldest to Newest.

4. In cell G4, type the label Sort Observation. In G5, write a quick note about what you notice (e.g., “Budget Review is due soonest”).

Tip: Sorting doesn’t change your data—it just displays it differently. You can always undo (Ctrl + Z) to revert.

Step 5: Filter Your Data

Filtering lets you focus on specific data, like tasks assigned to one person or with a certain status.

Instructions:

1. Click the filter arrow in the Assigned To column (B1).

2. Uncheck “Select All”, then check Sarah Johnson and click OK.

   • Notice how only Sarah’s task(s) appear.

3. Clear the filter: Click the filter arrow again and select Clear Filter from ‘Assigned To’.

4. Now filter by Status:

   • Click the filter arrow in the Status column (E1).

   • Uncheck “Select All”, check In Progress, and click OK.

5. In cell G7, type the label Filter Observation. In G8, note what you see (e.g., “Two tasks are In Progress”).

6. Clear all filters: Go to the Data tab and click Clear (or recheck “Select All” in the Status filter).

Tip: Filters hide rows temporarily—you’re not deleting anything. Use filters to drill down into your data without altering it.

Checkpoint

By now, your Task Tracker worksheet should have:

• A formatted dataset of tasks in an Excel table named “TaskTable”.

• Evidence of sorting (you can keep it sorted by Due Date or revert to original order).

• Observations in cells G5 and G8 about your sorting and filtering results.

• One additional row added to confirm the table’s dynamic resizing.

Great work! You’ve transformed raw data into a dynamic table, sorted it to uncover insights, and filtered it to focus on specific details. These skills are essential for managing lists, tracking progress, or analyzing information in any workplace. Keep practicing, and you’ll find tables make data management faster and more intuitive. As a reminder, you’ll upload the Task Tracker worksheet as part of your completed Excel workbook in your self-evaluation.

Data Cleaning Basics

Messy data can lead to errors and unreliable insights, whether you’re managing customer lists, tracking orders, or preparing reports. In this module, you’ll learn three essential Excel tools to clean data: removing duplicates, splitting text with "Text to Columns," and trimming unwanted spaces. These skills will help you prepare data for analysis or reporting, saving time and boosting accuracy. Let’s tidy up your data!

Step 1: Start in Your Existing Workbook

• Open the Excel workbook you’ve been using for previous modules.

• Add a new worksheet by clicking the plus (+) icon at the bottom.

• Rename the tab: Customer Cleanup.

Step 2: Set Up Your Data

In your new Customer Cleanup sheet, you’ll create a dataset representing a customer list with common data issues (duplicates, combined names, and extra spaces). Starting in cell A1, enter the following table:

Customer Name Email City

John   Smith john.smith@email.com New York

Maria  Garcia maria.g@email.com Los Angeles

John Smith john.smith@email.com New   York

Aisha   Khan aisha.khan@email.com Chicago

Carlos Rivera carlos.r@email.com Miami

Maria Garcia maria.g@email.com Los  Angeles

Emma   Wilson emma.wilson@email.com Seattle

Instructions:

• Type the data exactly as shown, starting in cell A1 (so “Customer Name” is in A1, and data begins in A2).

  • Note the intentional issues: extra spaces in names (e.g., "John Smith"), duplicates (John Smith and Maria Garcia appear twice), and inconsistent spacing in cities (e.g., "New York").

• Make the header row (A1:C1) bold.

• Adjust column widths if needed by double-clicking column borders to auto-fit content.

• In cell E1, type the label Original Row Count. In E2, count the rows of data (excluding the header): =COUNTA(A2:A8). This should show 7.

Tip: Real-world data often has these kinds of errors, and cleaning them is a critical first step before analysis or reporting.

Step 3: Remove Duplicates

Duplicates can skew analysis, like counting the same customer twice. Let’s clean them up.

Instructions:

1. Select the entire dataset (cells A1:C8).

2. Go to the Data tab on the ribbon and click Remove Duplicates.

3. In the dialog box, ensure all columns (Customer Name, Email, City) are checked to identify exact matches, then click OK.

   • Excel will show a message indicating how many duplicates were removed (expect 2 duplicates removed, leaving 5 unique records).

4. The table should now have 5 rows (plus the header).

5. In cell E4, type Cleaned Row Count. In E5, recalculate the row count: =COUNTA(A2:A6). This should show 5.

6. In cell E7, type Duplicates Observation. In E8, note what happened (e.g., “Two duplicate customers were removed, leaving 5 unique records”).

Tip: Removing duplicates checks for exact matches across selected columns, so all data (including spaces) must be identical. We’ll fix spaces later to catch more inconsistencies.

Step 4: Use Text to Columns

The Customer Name column has full names combined. Let’s split them into first and last names for better usability.

Instructions:

1. Insert two new columns to the right of Customer Name:

   • Right-click the column B header (Email), select Insert twice to add two columns.

   • Label B1 as First Name and C1 as Last Name. (Email and City shift to D and E.)

2. Select the Customer Name data (cells A2:A6, after duplicates are removed).

3. Go to the Data tab and click Text to Columns.

4. In the wizard:

   • Choose Delimited and click Next.

   • Check Space as the delimiter (uncheck others) and click Next.

   • Set the destination as B2 (to place First Name in B2 and Last Name in C2). Click Finish.

5. Check the results: First names (e.g., John, Maria) should be in column B, last names (e.g., Smith, Garcia) in column C.

6. Make headers B1 and C1 bold.

7. In cell G1, type Text to Columns Observation. In G2, note the outcome (e.g., “Names split into first and last, making it easier to sort or filter”).

Tip: Text to Columns is powerful for splitting data (e.g., addresses, dates) but relies on consistent delimiters like spaces or commas. Extra spaces might cause issues, which we’ll fix next.

Step 5: Trim Extra Spaces

Extra spaces in names and cities can cause problems (e.g., "New   York" vs. "New York" might not match). Let’s clean them up.

Instructions:

1. Create a new column to clean Customer Name data:

   • Right-click the column A header, select Insert, and label A1 as Cleaned Name.

2. In cell A2, use the TRIM function to remove extra spaces from the original Customer Name (now in D2 after inserting columns):

   • Type =TRIM(D2) and press Enter.

   • Copy the formula down to A3:A6 (drag the fill handle or double-click it).

3. Convert these to values to finalize:

   • Select A2:A6, copy (Ctrl + C), right-click A2, and choose Paste Values (under Paste Options).

4. Repeat for the City column (now in F after shifts):

   • Insert a new column at E (right-click, Insert), label E1 as Cleaned City.

   • In E2, type =TRIM(F2) and copy down to E3:E6.

   • Copy E2:E6, paste as values in E2 using Paste Values.

5. Delete the original columns to tidy up:

   • Select column D (original Customer Name), hold Ctrl, select column F (original City), right-click, and choose Delete.

6. In cell G4, type Trim Observation. In G5, note the impact (e.g., “Extra spaces removed from names and cities, ensuring consistency”).

Tip: TRIM removes leading, trailing, and multiple spaces between words, leaving single spaces. Pasting as values locks in the cleaned data to avoid formula dependencies.

Checkpoint

By now, your Customer Cleanup worksheet should have:

• A cleaned dataset with no duplicates (5 customer records).

• Customer names split into First Name and Last Name columns.

• Extra spaces removed from names and cities, with cleaned data in columns A (Cleaned Name) and E (Cleaned City).

• Observations in cells E8, G2, and G5 explaining what each step achieved.

• Row counts in E2 and E5 showing the effect of removing duplicates.

Awesome work! You’ve tackled three common data issues—duplicates, combined text, and extra spaces—making your customer list ready for analysis, reporting, or sharing. These cleaning skills are foundational for working with real-world data, ensuring accuracy and professionalism. As a reminder, you’ll upload the Customer Cleanup worksheet as part of your completed Excel workbook in your self-evaluation.

Using AI with Excel

Artificial intelligence tools like Grok and ChatGPT can transform how you work in Excel, making complex tasks faster and easier. In this module, you’ll learn to use AI to generate formulas, uncover data insights, and automate repetitive work. Whether you’re calculating budgets, summarizing sales, or cleaning datasets, AI can save time and boost accuracy. Let’s explore how to supercharge your Excel skills with AI!

Note: For this module, you’ll need access to an AI tool like ChatGPT (via chat.openai.com, free with an account) or Grok (via X Premium, if available). If you don’t have access, follow along with the provided AI responses. You’ll also need Excel (any recent version).

Step 1: Start in Your Existing Workbook

• Open the Excel workbook you’ve been using for previous modules.

• Add a new worksheet by clicking the plus (+) icon at the bottom.

• Rename the tab: AI Insights.

Step 2: Set Up Your Data

In your AI Insights sheet, create a dataset of customer feedback to use with AI for formulas, analysis, and automation. Starting in cell A1, enter:

Customer ID Feedback Rating Date

C001 Great service, fast delivery 5 3/1/2025

C002 Product ok, shipping slow 3 3/2/2025

C003 Excellent quality 5 3/3/2025

C004 Slow response, good product 4 3/4/2025

C005 Poor packaging, item damaged 2 3/5/2025

Instructions:

• Enter the data as shown, starting in A1.

• Bold the header row (A1:D1).

• Format Rating (C2:C6) as Number with no decimals.

• Format Date (D2:D6) as Short Date (right-click, Format Cells > Date).

• Auto-fit column widths by double-clicking column borders.

• In F1, label Average Rating. In F2, leave blank for now (we’ll use AI to fill it).

Tip: This dataset mimics real-world feedback, perfect for testing AI’s ability to calculate, analyze, and clean.

Step 3: Use AI to Generate Formulas

AI can write Excel formulas for you, saving time on complex calculations. Let’s calculate the average rating.

Instructions:

1. Open your AI tool (e.g., ChatGPT at chat.openai.com or Grok via X, if accessible). If you don’t have access, use the sample prompt and response below.

2. Enter this prompt:

   • “Write an Excel formula to calculate the average of numbers in cells C2 to C6.”

3. Expected AI response (e.g., from ChatGPT or Grok):

   • =AVERAGE(C2:C6)

4. In Excel, type the formula =AVERAGE(C2:C6) into F2. Press Enter (should show 3.8).

5. Try another formula:

   • Prompt: “Write an Excel formula to count how many ratings are 5 in cells C2 to C6.”

   • Expected response: =COUNTIF(C2:C6, 5)

   • Enter =COUNTIF(C2:C6, 5) in F4 (label F3 as Count of 5s). Should show 2.

6. In G1, label Formula Observation. In G2, note how AI helped (e.g., “AI gave me the exact formula to average ratings without looking it up”).

Tip: Be specific in your AI prompts (e.g., mention cell ranges) to get accurate formulas. If the formula doesn’t work, ask AI to clarify or try rephrasing.

Step 4: Analyze Data with AI

AI can summarize trends or insights from your data, enhancing decision-making.

Instructions:

1. Copy the Feedback column (B2:B6) to your clipboard (Ctrl + C).

2. In your AI tool, paste the feedback and prompt:

   • “Analyze this customer feedback and summarize key themes in a few sentences.”

3. Expected AI response (example):

   • “The feedback highlights mixed experiences. Positive themes include great service, fast delivery, and excellent quality (3 customers). Negative themes include slow shipping, slow response, and poor packaging (2 customers).”

4. In Excel, paste this summary into G4 (label G3 as Feedback Summary). Resize the cell or wrap text (Home > Wrap Text) to fit.

5. Try a trend analysis:

   • Prompt: “Based on ratings 5, 3, 5, 4, 2, what’s the overall customer sentiment?”

   • Expected response: “The ratings suggest a generally positive sentiment, with an average of 3.8. Most customers (3 of 5) gave 4 or 5, but one low rating (2) indicates some dissatisfaction.”

   • Paste this into G7 (label G6 as Rating Trend).

6. In G9, label Analysis Observation. In G10, note AI’s value (e.g., “AI quickly spotted positive and negative feedback trends”).

Tip: AI excels at summarizing text or numbers—use it to find patterns you might miss, like common complaints.

Step 5: Automate a Task with AI

AI can suggest ways to clean or automate repetitive tasks, like standardizing data.

Instructions:

1. Notice the Feedback column has inconsistent lengths. Let’s use AI to shorten long entries.

2. Prompt your AI tool:

   • “Suggest an Excel formula or method to shorten text in cells B2:B6 to 20 characters or less, keeping the main idea.”

3. Expected response (example):

   • “Use the LEFT function: =LEFT(B2, 20) to take the first 20 characters. For meaningful summaries, manually trim or use AI to rewrite shorter versions.”

4. In Excel, insert a new column at B:

   • Right-click column B header, select Insert, label B1 as Short Feedback.

   • In B2, type =LEFT(C2, 20), copy down to B3:B6 (Feedback shifts to C).

5. Check results (e.g., “Great service, fast del” for B2). If unclear, try AI again:

   • Prompt: “Rewrite ‘Great service, fast delivery’ to 20 characters or less.”

   • Response: “Great service, fast”

   • Manually enter this in B2 for accuracy.

6. In G12, label Automation Observation. In G13, note the outcome (e.g., “AI’s formula shortened feedback, but I tweaked one manually for clarity”).

Tip: AI can suggest automation ideas, but test them in Excel to ensure they fit your needs. Short formulas like LEFT are quick wins for cleaning.

Checkpoint

Your AI Insights worksheet should have:

• A dataset (A1:D6) with customer feedback, ratings, and dates.

• Formulas in F2 (=AVERAGE(C2:C6)) and F4 (=COUNTIF(C2:C6, 5)).

• A new Short Feedback column (B2:B6) with shortened text.

• AI-generated summaries in G4 (feedback themes) and G7 (rating trend).

• Observations in G2, G10, and G13 explaining AI’s impact.

Awesome work! You’ve harnessed AI to generate formulas, analyze feedback, and automate text trimming, making Excel more powerful. These skills let you tackle complex tasks with confidence, from reports to data prep. As a reminder, you’ll upload the AI Insights worksheet as part of your completed Excel workbook in your self-evaluation.