Excel

Excel Chapter 1 - Creating and Editing Workbooks

Excel Chapter 2 - Working with Formulas and Functions

• Work in Normal view (otherwise, you might see different ruler measurements when doing things like changing row height)

• Name Manager defined names is important for chapter 2 guided project

• Defined names are inherently absolute references

Excel Chapter 3 - Creating and Editing Charts

• The Quick Analysis button comes up after selecting a set of data like the Quick Toolbox in Word

• “Make a table” instruction - highlight across row with formulas and then autofill down

• Line with markers chart

• Center Across Selection

• Selecting multiple columns using Ctrl

• Add chart element button doesn’t appear on Mac, need to go to Chart Design, Add Chart Element

Excel Chapter 4 - Getting and Managing Data

• Sort & Filter - Advanced might not work on a Mac

Excel Chapter 5 - Exploring Data Tools, Data Types, and Illustrations

• Ctrl + F6 to switch sheets

• Static vs Dynamic

• Consolidation by category - for when arrangement is not the same, column or row labels are used to match

• Take a minute to understand what you are looking at

• Group sheets (Ctrl click) to perform the same operation on them

• You may need to sign out of a personal account and sign in with your FGCU account to use linked data types

Excel Chapter 6 - Exploring the Function Library 

Review

F4 to toggle between relative, absolute, and mixed references.

🧭 Navigating Nested Functions in Excel

1. Using the Name Box Drop-Down Arrow

• Review: The Name Box is located to the left of the formula bar. You can use the Name Manager to see set names. 

• F3 to paste a name.

• When you're editing a formula with nested functions, clicking the drop-down arrow in the Name Box can show you named ranges or function components (depending on context).

• In some versions of Excel, especially when using the Function Arguments dialog, this drop-down helps you jump to specific parts of a complex formula.

2. Clicking the Top-Level Function Name in the Formula Bar

• When you click on a cell with a nested formula, the Formula Bar shows the entire formula.

• If you click directly on the name of the top-level function (e.g., IF in the example above), Excel will often open the Function Arguments dialog for that function.

• This dialog helps you edit the inputs to the function in a structured way.

• If you’ve drilled down into a nested function (like editing SUM inside IF), clicking the top-level function name lets you return to the outer function.

Programming Operations in Excel

🔧 Excel: Ubiquitous but Limited in Scope

✅ Why Advanced Formulas Are Common

• Accessibility: Most business users already have Excel and know the basics.

• Built-in Functions: Excel has hundreds of functions for math, stats, finance, text, and logic.

• No Coding Required: You can build complex logic without writing code.

• Immediate Feedback: You see results instantly in cells.

❌ Limitations

• Scalability: Large datasets slow Excel down.

• Maintainability: Nested formulas can become unreadable and error-prone.

• Automation: Limited unless you use VBA or integrate with Power Query or Office Scripts.

• Version Control: Difficult to track changes or collaborate on logic.

🐍 Python: Powerful and Scalable

✅ Why Python Might Be Better

• Readability: Code is often easier to understand and maintain than deeply nested Excel formulas.

• Libraries: Tools like pandas, numpy, and openpyxl make data manipulation powerful and flexible.

• Automation: You can automate tasks across files, folders, or even web APIs.

• Scalability: Handles large datasets with ease.

• Reusability: Functions and scripts can be reused and shared easily.

❌ Challenges

• Learning Curve: Requires programming knowledge.

• Setup Overhead: Needs Python environment and possibly IDEs like VS Code or Jupyter.

• Less Visual: No built-in grid interface like Excel for quick visual analysis.

🧠 So, Which One to Use?

• Use Excel when:

  • You need quick analysis or dashboards.

  • You're collaborating with non-technical users.

  • The dataset is small to medium-sized.

  • You want to avoid writing code.

• Use Python when:

  • You're working with large or complex datasets.

  • You need automation or integration with other systems.

  • You want reproducibility and version control.

  • You're comfortable with coding or want to teach students programming skills.

Database functions in Excel

Here's a side-by-side example of how a simple database-like task would be handled in Excel using a formula vs SQL using a query.

🧮 Scenario: You have a dataset of sales records with columns:

• Region

• Salesperson

• SalesAmount

You want to calculate the total sales for the "East" region.

✅ Excel Formula (Database Function)

Assuming your data is in a table named SalesData, and the columns are in:

• A2:A100 for Region

• C2:C100 for SalesAmount

You could use:

Excel

=DSUM(SalesData, "SalesAmount", A1:B2)

Where:

• SalesData is the named range of your table.

• "SalesAmount" is the field you're summing.

• A1:B2 is a criteria range like:

A1: Region

A2: East

Alternatively, using a more modern formula:

Excel

=SUMIFS(C2:C100, A2:A100, "East")

🗃️ SQL Equivalent

SELECT SUM(SalesAmount)

FROM SalesData

WHERE Region = 'East';

Excel Chapter 7 - Working with Templates and Collaboration

• Review: Budgeting templates

• Templates related to your major / interests

• File command tab and choose Options, Select Customize Ribbon in the left pane, Select the Developer box in the Main Tabs group

Excel Chapter 8 - Developing and Using Macros