Excel Workbooks and Worksheets Introduction

1. Creating a New Workbook

• Launch Excel.
• Click on 'File' > 'New' > 'Blank Workbook'.

2. Navigating Worksheets

• Open Excel.
• Find tabs at the bottom labeled 'Sheet1', 'Sheet2', etc.
• Click the '+' icon to add a new sheet.
• Right-click on a sheet tab to rename, delete, or rearrange sheets.

3. Entering Data

1. Select a cell (e.g., A1).
2. Type in the desired value.
3. Press 'Enter' to move to the next cell.

4. Using Formulas

• Select cell where you want the result.
• Type the equals sign = to start a formula.
• Example to sum values in cells A1 to A3:

  =SUM(A1:A3)
  

• Press 'Enter'.

5. Built-in Tools

Sort and Filter

• Select the data range.
• Click on 'Data' tab.
• Choose 'Sort' or 'Filter'.

PivotTables

• Select the data range.
• Click 'Insert' tab > 'PivotTable'.
• Drag fields to appropriate areas in PivotTable Field List.

Charts

• Select the data range.
• Click on 'Insert' tab.
• Choose a chart type (e.g., 'Column', 'Line').

6. Saving the Workbook

• Click on 'File' > 'Save As'.
• Choose location and file format.
• Click 'Save'.

Navigating and Managing Worksheets

1. Switching Between Worksheets

• Use the tabs at the bottom of your Excel window.

2. Renaming a Worksheet

1. Double-click the worksheet tab.
2. Type the new name and press Enter.

3. Adding a New Worksheet

• Click the "+" icon next to the existing worksheet tabs.

4. Deleting a Worksheet

1. Right-click the tab of the worksheet.
2. Select "Delete".

5. Moving or Copying a Worksheet

1. Right-click the worksheet tab.
2. Select "Move or Copy".
3. Choose the destination and check "Create a copy" if needed, then click "OK".

6. Hiding/Unhiding Worksheets

• To Hide:
  1. Right-click the tab of the worksheet.
  2. Select "Hide".
• To Unhide:
  1. Right-click any worksheet tab.
  2. Select "Unhide" and pick the worksheet.

7. Grouping Worksheets

• Select multiple worksheets by holding down Ctrl (Windows) or Command (Mac) and clicking the sheets you want to group.

8. Ungrouping Worksheets

• Right-click one of the selected tabs and select "Ungroup Sheets".

9. Navigating to a Specific Worksheet

1. Right-click the single arrow navigation button.
2. Choose from the list of all sheets.

10. Rearranging Worksheets

1. Click and hold the worksheet tab.
2. Drag it to the desired position.

11. Protecting a Worksheet

1. Go to the "Review" tab.
2. Click "Protect Sheet".
3. Set a password and check options as needed, then click "OK".

Formulas and Tools Usage

12. Applying Formulas

• Example: Calculating Sum
  • Select the cell.
  • Enter =SUM(A1:A10) and press Enter.

13. Using Built-in Tools

• Example: Conditional Formatting
  1. Select the range.
  2. Go to "Home" > "Conditional Formatting".
  3. Choose and set the rules.

This implementation can be utilized directly in Excel for effective worksheet management and data analysis.

3. Working with Basic and Advanced Formulas

Basic Formulas

• SUM Function

  =SUM(A1:A10)
  

  Sum of values in range A1 to A10.

• AVERAGE Function

  =AVERAGE(A1:A10)
  

  Average of values in range A1 to A10.

• MIN Function

  =MIN(A1:A10)
  

  Minimum value in range A1 to A10.

• MAX Function

  =MAX(A1:A10)
  

  Maximum value in range A1 to A10.

• IF Function

  =IF(A1 > 10, "Greater", "Smaller or Equal")
  

  Checks if A1 is greater than 10; returns "Greater" if true, "Smaller or Equal" otherwise.

Advanced Formulas

• VLOOKUP Function

  =VLOOKUP(A1, B1:D10, 2, FALSE)
  

  Look up value in A1, find it within the first column of the range B1 to D10, and return the corresponding value in the second column from the range. FALSE denotes exact match.

• INDEX and MATCH Functions

  =INDEX(B1:B10, MATCH(A1, A1:A10, 0))
  

  Returns the value in the range B1:B10 at the position where A1 matches in A1:A10.

• COUNTIF Function

  =COUNTIF(A1:A10, ">10")
  

  Count how many cells in the range A1 to A10 are greater than 10.

• SUMIF Function

  =SUMIF(A1:A10, ">10", B1:B10)
  

  Sum values in the range B1:B10 where the corresponding value in A1:A10 is greater than 10.

• CONCATENATE Function (or &)

  =CONCATENATE(A1, " ", B1)
  

  =A1 & " " & B1
  

  Combine text in cells A1 and B1 with a space in between.

Array Formulas

• TRANSPOSE Function

  =TRANSPOSE(A1:A10)
  

  Convert a vertical range (A1:A10) to a horizontal range or vice versa.

• SUMPRODUCT Function

  =SUMPRODUCT(A1:A10, B1:B10)
  

  Multiply corresponding elements in the ranges A1:A10 and B1:B10, then sum the products.

Logical Functions

• AND Function

  =AND(A1 > 10, B1 < 5)
  

  Returns TRUE if both conditions are true, otherwise FALSE.

• OR Function

  =OR(A1 > 10, B1 < 5)
  

  Returns TRUE if either condition is true, otherwise FALSE.

This covers core basic and advanced formulas in Excel for practical implementation in data analysis tasks.

Data Cleaning and Preparation Techniques in Excel

Removing Duplicates

1. Select Data Range:

   • Click on any cell within the data range.

2. Remove Duplicates:

   • Navigate to the Data tab.
   • Click Remove Duplicates.
   • In the pop-up, choose the columns to check for duplication.
   • Click OK.

Handling Missing Data

1. Identify Missing Data:

   • Use Conditional Formatting to highlight blanks.
   • Select data range.
   • Go to Home > Conditional Formatting > New Rule.
   • Select Use a formula to determine which cells to format.
   • Input formula: =ISBLANK(A1) assuming starting from A1.
   • Choose a format and click OK.

2. Fill Missing Data:

   • Use Go To Special.
   • Press F5, click Special, then Blanks.
   • Enter value/formula (e.g., mean, median, previous value) and press Ctrl + Enter.

Standardizing Data

1. Text Case Standardization:

   • For proper case: Use formula =PROPER(A1).
   • For uppercase: Use formula =UPPER(A1).
   • For lowercase: Use formula =LOWER(A1).

2. Trimming Spaces:

   • Use formula =TRIM(A1) to remove leading/trailing spaces.

Splitting and Combining Data

1. Splitting Data:

   • Select column to split (e.g., Full Name).
   • Navigate to Data > Text to Columns.
   • Choose delimiter (e.g., space).
   • Follow prompts and press Finish.

2. Combining Data:

   • Use formula =A1 & " " & B1 to combine first and last name.

Removing Unwanted Characters

1. Remove Specific Characters:

   • Use SUBSTITUTE(A1, "char_to_remove", "").

2. Remove Non-Printable Characters:

   • Use CLEAN(A1).

Filtering Data

1. Applying Filter:
   • Click on any cell in the data range.
   • Navigate to Data > Filter.
   • Use dropdown arrows to filter specific values.

Data Validation

1. Validate Data Entry:
   • Select cells to validate.
   • Navigate to Data > Data Validation.
   • Choose criteria (e.g., whole number, date).
   • Set validation conditions and click OK.

Data Transformation

1. Pivot Table:

   • Select data range.
   • Navigate to Insert > PivotTable.
   • Drag fields to report areas (Columns, Rows, Values).

2. Transpose Data:

   • Copy data range.
   • Right-click on destination cell.
   • Select Paste Special > Transpose.

---

These procedures should help prepare your data within Excel for further analysis.

Analyzing Data with PivotTables

Step-by-Step Implementation

1. Insert a PivotTable

1.1. Select the range of data you want to analyze.
1.2. Go to the Insert tab on the Ribbon.
1.3. In the Tables group, click PivotTable.
1.4. In the Create PivotTable dialog box, select a location for the PivotTable (New Worksheet or Existing Worksheet).
1.5. Click OK.

2. Configure the PivotTable Fields

2.1. The PivotTable Fields pane will appear.
2.2. Drag fields into the four areas: Filters, Columns, Rows, and Values.

• Rows: Drag fields here to display data grouped by row.
• Columns: Drag fields here to show data grouped by column.
• Values: Drag fields here to perform calculations on the data.
• Filters: Drag fields here to add filtering options above the PivotTable.

Example

Suppose you have sales data with columns: Date, Region, Product, Sales

1. Insert the PivotTable:

- Select range A1:D1000 (example data range).
- Go to `Insert` > `PivotTable`.
- Select `New Worksheet` and click `OK`.

2. Configure the PivotTable Fields:

Rows:

• Drag Region to Rows area.

Columns:

• Drag Product to Columns area.

Values:

• Drag Sales to Values area.
• Ensure it displays as SUM of Sales.

Filters:

• Drag Date to Filters area if you want to filter by specific dates.

3. Format the PivotTable (Optional):

3.1. Click anywhere in the PivotTable.
3.2. Go to the Design tab on the Ribbon.
3.3. Select a style for the PivotTable.

4. Using the Filters (Optional):

4.1. Click the drop-down arrow next to the field in the Filters area.
4.2. Select the items to include and click OK.

Conclusion

Your PivotTable should now be ready and displaying summarized data based on the fields you configured. This is a practical approach to analyze data using Excel PivotTables.

Visualizing Data with Charts and Graphs in Excel

1. Create a Chart from Data

1. Select the data range:

   Highlight the range of cells that contains the data you want to visualize.
   

2. Insert a Chart:

   Go to the 'Insert' tab.
   Select the type of chart you want from the 'Charts' group (e.g., Column, Line, Pie).
   

2. Customize the Chart

1. Change Chart Title:

   Click on the chart title.
   Type a new title and press Enter.
   

2. Adjust Axis Titles:

   Click on the axis title.
   Type a new title and press Enter.
   

3. Modify Legend:

   Right-click on the legend.
   Select 'Format Legend' and adjust the settings.
   

3. Format Data Series

1. Change Series Colors:

   Right-click on the data series.
   Select 'Format Data Series'.
   Choose the desired fill color.
   

2. Add Data Labels:

   Right-click on the data series.
   Select 'Add Data Labels'.
   

4. Filter Data in a Chart

1. Use Chart Filters:

   Click on the chart.
   Click the filter icon next to the chart.
   Select or deselect the data series you want to display.
   

5. Enhance Chart Appearance

1. Apply Chart Styles:

   Select the chart.
   Go to the 'Chart Tools Design' tab.
   Choose a style from the 'Chart Styles' group.
   

2. Adjust Chart Layout:

   Go to the 'Chart Tools Layout' tab.
   Use the layout options to modify elements like titles, labels, and gridlines.
   

6. Saving the Chart as a Template

1. Save Chart Template:

   Right-click on the chart.
   Select 'Save as Template...'.
   Choose a name and location for the template file.
   

7. Inserting the Chart into Another Worksheet or Workbook

1. Copy Chart to Another Worksheet:

   Right-click on the chart.
   Select 'Copy'.
   Navigate to the desired worksheet.
   Right-click and select 'Paste'.
   

2. Move Chart to a New Worksheet:

   Right-click on the chart.
   Select 'Move Chart...'.
   Choose 'New sheet' and click 'OK'.
   

Part 7: Automating Tasks with Macros

Creating a Macro

1. Open the Excel Workbook:

   • Ensure your Excel workbook is open.

2. Enable the Developer Tab:

   • If not already visible, enable the Developer tab:
     • Go to File > Options > Customize Ribbon.
     • Check the Developer checkbox.

3. Record a Macro:

   • Go to the Developer tab.
   • Click on Record Macro.
   • Enter a name for the macro (No spaces, e.g., FormatReport).
   • Optionally, assign a shortcut key.
   • Select where to store the macro (This Workbook for current workbook).
   • Click OK.

4. Perform the Tasks You Want to Automate:

   • Execute the actions you want to automate (e.g., formatting cells, applying formulas).

5. Stop Recording:

   • Go back to the Developer tab.
   • Click Stop Recording.

Running a Macro

1. Run the Macro from the Developer Tab:

   • Go to Developer > Macros.
   • Select the macro (e.g., FormatReport).
   • Click Run.

2. Run the Macro with a Shortcut Key:

   • Use the assigned shortcut key (if any) to run the macro directly.

Editing a Macro

1. Open the Macro:

   • Go to Developer > Macros.
   • Select the macro and click Edit.
   • This opens the Visual Basic for Applications (VBA) editor.

2. Edit the Macro Code:

   • Make changes to the macro script as needed.
   • Example: Change the cell formatting code.

     Sub FormatReport()
         ' Select the range A1:A10
         Range("A1:A10").Select
         ' Apply bold formatting
         Selection.Font.Bold = True
         ' Apply cell color
         Selection.Interior.Color = RGB(220, 230, 241)
     End Sub
     

   • Close the VBA editor after making changes.

Assigning Macros to Buttons

1. Insert a Button:

   • Go to Developer > Insert > Button (Form Control).
   • Draw the button on the worksheet.

2. Assign the Macro:

   • After placing the button, the Assign Macro dialog box appears.
   • Select the macro to assign (e.g., FormatReport).
   • Click OK.

3. Rename the Button (Optional):

   • Right-click the button and select Edit Text.
   • Change the text to a meaningful name (e.g., Run Report Formatter).

By following these steps, you can automate repetitive tasks in Excel using macros, streamline your workflow, and improve productivity efficiently.