Excel Basics Session 1

When you launch Excel, you can start with a 'Blank Workbook,' which is an empty spreadsheet ready for you to enter data and create calculations.

Columns run vertically (like letters A, B, C), rows run horizontally (like numbers 1, 2, 3), and cells are the intersections of a column and row (for example, A1).

You can change the name of a cell (e.g., A1) to something more descriptive like 'ProductName' or 'SalesTotal' for better organization and readability.

You can specify the type of data a cell should hold, for example: numbers, text, dates, currency, or percentages.

AutoFill allows you to quickly copy formulas or values across multiple cells by dragging the bottom-right corner of a selected cell.

You can copy the contents of a cell using keyboard shortcuts (Ctrl + C or Cmd + C for copy, and Ctrl + V or Cmd + V for paste).

Save your work as an Excel Workbook (.xlsx) to keep your data, or as a Template (.xltx) to create new workbooks based on the same structure.

Excel on Windows allows importing data from various sources like text files (CSV), web pages, and databases. It uses Power Query which integrates seamlessly, providing a flexible and powerful way to bring data into your spreadsheet.

Excel for Mac has similar import capabilities but is more limited, especially for web data. Older versions might not support direct website imports, requiring workarounds like using saved HTML files. Power Query integration is also limited on Mac.

Conditional formatting in Excel for Windows allows for more precise control over Data Bars and Icon Sets. You can finely adjust minimum and maximum values for Data Bars and have a wide range of icon options.

Conditional formatting on Mac offers fewer preset icon choices compared to Windows. Customization options, like adjusting Data Bar's minimum and maximum values, are also less flexible.

Sorting data in Excel allows you to arrange data in ascending or descending order based on a chosen column. It helps organize and analyze data efficiently.

Filtering data in Excel lets you select specific data based on specific criteria, such as text values, numbers, or dates. It helps you focus on relevant information.

Grouping data in Excel allows organizing data into collapsible sections, making it easier to navigate and analyze large datasets.

Freezing Panes helps keep certain rows or columns visible while scrolling through the rest of the spreadsheet. This is especially useful for keeping headers or important rows visible even when browsing large datasets.

Excel allows you to find specific text or values in a spreadsheet and replace them with something else. This feature is useful for correcting errors or making mass changes to data.

You can quickly locate specific text or values within your spreadsheet using the 'Find' feature, which allows you to search through the entire workbook.

The 'Replace' feature lets you change all occurrences of a specific text or value within your spreadsheet with something else.

Combine multiple adjacent cells into a single larger cell to display a single entry across them.

Use this tool to find the input value needed to achieve a specific result in a formula. For example, find the sales needed to reach a target profit.

Create a table that shows how changes in one or two variables affect the outcome of a formula, helping you analyze the impact of different input values.

Create and compare different scenarios (sets of input values) to see how they affect outcomes, such as best-case and worst-case scenarios.

Combine data from multiple worksheets or workbooks into a single summary table, using functions like SUM, AVERAGE, etc.

When consolidating data, you choose a 'Function' (like Sum, Average, etc.) to determine how data from different sources will be combined.

In consolidation, you add 'References' to specific ranges of cells in different worksheets or workbooks, indicating the data you want to combine.

When consolidating data, you can check 'Top row' or 'Left column' if your data has labels, ensuring labels are included in the final summary.

Restricting cell input to a predefined set of options displayed as a dropdown menu. You can create this in Excel by selecting cells, navigating to the Data tab, and choosing 'Data Validation'. Then, set 'Allow' to 'List' and input your list items.

Changing the way data is presented in a cell, which can involve changing its format to Number, Date, Currency, or other options. You can format cells by right-clicking on a cell and selecting 'Format Cells' (or using the Format menu on older Macs).

Using specific key combinations for common actions, like copying (Ctrl+C, Cmd+C), pasting (Ctrl+V, Cmd+V), and auto-summing (Alt+=, Cmd+Shift+T), which speeds up data entry and manipulations.

Displays the current date, like '2024-10-26'. You can use it to automatically display the current date without having to type it manually.

Solver is a tool in Excel used to find the best solution for a problem by changing certain variables while respecting constraints. This helps achieve goals like maximizing profits or minimizing costs.

To enable Solver in Excel, go to the 'File' menu, then 'Options', then 'Add-ins'. Select 'Solver Add-in' and click 'Go'. Check the box for Solver and click 'OK'.

A Pivot Table is a powerful tool in Excel for summarizing and analyzing large datasets. It allows you to group and aggregate data based on different criteria.

Select your data range, go to 'Insert', and choose 'PivotTable'. Select where you want to place it (new sheet or existing).

The IF function tests a condition and returns different results based on whether it's true or false. For example, it can tell you if a student passed or failed an exam based on their score.

The SUMIF function adds up values that meet a specific condition. For example, you could sum up sales for all 'Red' products.

COUNT counts only numbers, while COUNTA counts everything in a range that isn't blank, including numbers, text, and special characters.

The COUNTIF function counts how many cells meet a specific condition. For example, you could count how many students scored above 50 in an exam.

The AVERAGEIF function calculates the average of values that meet a specific condition. For example, you could find the average score of students who passed an exam.

What-If Analysis helps you explore different scenarios by changing input values and observing the impact on results. It's a way to understand the sensitivity of your calculations.