Excel Notes
Excel Basics and Workflows
Excel files are called workflows, and they contain worksheets.
Worksheets are composed of rows and columns that intersect to form cells (e.g., D5 refers to column D, row 5).
Each sheet has tabs at the bottom for navigation.
Rows, Columns, and Structure
Excel uses rows and columns for structured data arrangement, suitable for numerical and graded data.
This allows for easy data manipulation and analysis.
Office Split/File Tab as Control Center
The Office split or File tab serves as a control center for Excel files.
You can create new workbooks, open existing documents (using Ctrl+O), save work (using Ctrl+S), print (using Ctrl+P), and more.
Think of it as a filing cabinet for Excel.
Ribbon Interface
Excel uses a ribbon interface to organize commands, consisting of tabs, groups, and individual commands.
Tabs run across the top (e.g., Insert, Formula), and groups are categories related to the tabs.
This design helps users quickly find the features they need.
Cells as Building Blocks
Cells are the fundamental building blocks of Excel.
You can copy, cut, and paste content using commands on the Home tab.
Copy duplicates data, while cut removes and pastes it to a new location.
This makes rearranging spreadsheets efficient.
Formatting Text
Excel isn't just for numbers; you can format text to improve readability.
Options include bold, italicize, underline, font style and size changes.
Adding borders, changing text color, or applying fill color can highlight important information and enhance the spreadsheet's professionalism.
Conditional Formatting
Conditional formatting changes how cells look based on specified rules.
For example, highlight cells below 500 in red or grades above 90 in green.
Rules can be added, edited, or cleared as needed.
Conditional rules can apply different formats to specific data ranges, such as the top or bottom performers.
Inserting Rows and Columns
New rows can be inserted above, or new columns to the left of the selected cell.
This allows expansion of the worksheet without rebuilding it.
Excel always places the new row above and the new column to the left of the selected spot.
Fill Handle
The fill handle is a square in the lower right corner of a selected cell.
Dragging it copies content or extends patterns.
For example, entering 4 and 8 in cells, then dragging the fill handle, continues the series to 12, 16, 20, and so on.
It also works with days of the week and other patterns.
Sorting Data
Sorting helps organize data in alphabetical or numerical order.
You can sort from A to Z or from smallest to largest.
This is useful for organizing names or expenses.
Cell References
Cell formulas rely on cell references.
Relative reference (e.g., A1) changes when you move or copy the formula.
Absolute reference (e.g., ) remains fixed no matter where you copy it.
Mixed reference (e.g., or ) fixes one part and keeps the other relative.
This flexibility makes formulas more powerful.
Functions
Functions are built-in formulas in Excel.
Common functions include:
SUM: adds values.
IF: tests conditions.
COUNTIF: count function in the outfield meeting conditions in the left to right.
LEFT/RIGHT: extract characters from text.
NOW: returns current date and time.
These functions make Excel function like a calculator for data analysis.
Function Auditing
Function auditing helps trace precedence (which cells affect a formula) and dependents (which cells are affected by a formula).
This is important for troubleshooting formulas and complex spreadsheets.
Shortcut Keys
Useful shortcut keys include:
F2: edits the active cell.
Ctrl+D: fills down.
Alt+=: inserts SUM function.
Ctrl+;: inserts today's date.
Ctrl+Shift+%: applies percentage format.
Learning these shortcuts can save time and reduce errors.