Intro to Excel 365: Features and Origin

Introduction to Microsoft Excel 365

• Collaborative features in Excel 365 enhance teamwork by allowing multiple users to work on the same worksheet simultaneously and support combining data from various sources.
• Excel Pivot integrates large data volumes from various sources and enables connections between datasets for powerful analysis.
• Excel 365 is accessible online with a Microsoft account and offers a free version with cloud functionality.
• Access to premium features, updates, security enhancements, and bug fixes requires a monthly or yearly subscription.
• Excel 365 provides 1 TB of OneDrive cloud storage for secure document storage with global internet access.
• Excel 365 has a user-friendly interface similar to traditional Excel, designed for ease of use at all levels.
• Excel acts as a comprehensive user guide for creating accurate worksheets using formulas and templates.

Origin of Excel

• Microsoft has been around since the early 1980s.
• Excel gained prominence in 1987/1988 with the release of version 2.0.
• Excel version 5.0 included VBA, enabling advanced data analysis and reporting.
• The latest versions are Excel 2019 and Excel 365, known for their enhanced capabilities and business efficiency impact.
• When used with other Microsoft tools, Excel’s functionality improves significantly.

Meaning and Relevance of Excel

• Excel is a spreadsheet application for organizing and performing calculations on data.
• Excel is a tool for recording and analyzing data and also representing data on a graph or chart.
• Excel is composed of rows and columns for inputting and managing data.
• Excel allows the creation and editing of formulas.
• Excel is favored for its effectiveness in small and large offices.
• Excel is used for tracking expenditures and preparing monthly budgets.
• Excel is used for effective modelling and practical analysis of data.
• Excel is used for good finance and accounting analysis.
• Excel is used for creating a check and balance of reports and checkbooks.
• Excel makes it easier and faster perform work.
• Excel virtually develops new features for proper calculation on every new release, such as CONCATENATE and TEXTJOIN in Excel 2019.
• Excel is used for storing and manipulating data

What is Excel 365?

• Excel 365 is an online version of Excel operating on the web or cloud through a subscription.
• Excel 365 provides access to new features with each release and allows document saving on the cloud and local devices.

Differences Between Microsoft Excel Versions: Pricing

• Microsoft 365 Family costs PHP 756.67 monthly or PHP 7571.90 annually.
• Microsoft 365 Personal costs PHP 581.92 monthly or PHP 5824.40 annually.
• Traditional Excel (2016, 2019) requires a one-time purchase for lifetime use.
• Excel 365 offers continuous updates via subscription, while traditional Excel requires purchasing updated versions for new features.

Updated Versions and Features

• Excel 365 automatically receives updates for new versions, features, security, and bug fixes, downloadable with a single click, without additional costs beyond subscription fees.
• Traditional Excel does not provide automatic updates and requires substantial payments for newer versions, features, security, and bug fixes.
• Traditional Excel users who don't pay for updates must continue using outdated versions with existing features.

Document Saving Types (Autosaving or Auto Recover)

• Excel 365's Autosave feature automatically saves documents to OneDrive, ensuring no data loss during crashes or outages, with secure global access.
• Traditional Excel's Auto-Recover feature helps recover unsaved documents if specific conditions are met.
• Documents in traditional excel are lost permanently if the system crashes or is lost.

Collaboration

• Excel 365 allows multiple people to work on a single document simultaneously from anywhere via invitation links.
• Traditional Excel only allows one person to work on a document at a time, with no real-time collaboration possible.

Sub Ribbon Menu

• Excel 365 has fewer sub ribbons, focusing on basic data analysis tools with a compact design.
• Traditional Excel includes full sub ribbons with comprehensive tools for various data analysis tasks.

Similarities Between Excel 365 and Traditional Excel

• Both get downloaded into the computer
• Both are used as spreadsheets for recording and analyzing data.
• The window screen and menu tab are nearly identical on both.

Importance of Excel 365

• Excel 365 facilitates instant communication and real-time collaboration through online meetings and document sharing.
• Excel 365 scans documents every minute, protecting against malware.
• Excel 365 is cost-effective, offering a pay-as-you-go system for flexibility.
• Excel 365 provides 24/7 document and program access with a reliable internet connection.

What is a Workbook?

• An Excel workbook is a document containing one or more worksheets for organizing data.
• Excel workbooks can be created from blank documents or available templates.

Creating a New Excel Workbook

• To create from a blank document, open Excel from the Start menu, click New, then Blank Workbook, and enter data in each cell.
• To create from a template, open Excel, search for a template, double-click it, and enter data in each cell.

Excel Interface

• An Excel document is called a workbook; the default name for a new workbook is "Book 1".
• The ribbon is divided into tabs like File, Home, and Insert, each containing commands for specific tasks.
• The Name Box displays the current cell address, and the Formula Bar displays content of the selected cell.
• Columns arranged vertically are identified by letters (A, B, C,... XFD).
• Rows arranged horizontally are identified by numbers (1, 2, 3,... 1,048,576).
• Cells are the intersection of a row and a column (e.g., A1, B3, C5).
• A worksheet navigation button allows movement between worksheets in a workbook.
• The Status Bar indicates the current worksheet mode, which can be Ready, Enter, or Edit Mode.
• The plus (+) icon is used to add new worksheets to a workbook.
• The Worksheet is the main workspace for entering data and formulas for calculations.
• The scroll bar allows navigation to different parts of the worksheet.
• The zoom slider adjusts the view size of the worksheet.

Understanding Rows, Columns, and Cell Addresses

• Rows are identified by numbers (1, 2, 3, … 1,048,576) and run horizontally across the worksheet.
• Columns are identified by letters (A, B, C, … XFD) and run vertically in the worksheet.
• Each cell is the intersection of a row and a column, represented by column letter + row number (e.g., A1, B3).
• A cell range, identified by the first and last cell, is a group of two or more cells (e.g., A1:A8).

Excel Workbook

• A workbook is an Excel file/document containing one or more worksheets.
• A workbook is used to organize and arrange data in different groupings (worksheets).
• Depending on data size, a workbook can contain an unlimited number of worksheets.

Excel Worksheet

• A worksheet is a single page (spreadsheet) within a workbook where users can write, edit, and manage data.
• Worksheets consist of rectangular cells, the intersections of rows and columns.
• Each cell has an address (e.g., A1, B5) for referencing data.
• A workbook can contain multiple worksheets without any set limit.

Entering Date in the Worksheet Cell

• Data can be entered in single cells, multiple, or multiple worksheets at once.
• Available data types include text, numbers, dates, and time.
• A protected (locked) worksheet allows viewing but restricts editing to prevent accidental changes.
• To unprotect: Review tab → Changes group → Unprotect Sheet → OK (enter password if needed).

The Basic Knowledge of Entering Data in Excel

• Avoid empty rows and columns within a data range to ensure Excel features work correctly.
• Use descriptive text headers instead of numbers to prevent calculation errors.
• Keep similar data together and separate unrelated data with blank rows or columns.
• Text is aligned to the left and numbers to the right by default; check the format if alignment appears incorrect.
• Reduces formula errors and make calculations easier when Use cell references (e.g., A1, B3). Named ranges (e.g., "SalesData" for A1:A10),
• Don't type unit symbols with numbers, instead, enter numbers first, and then format the cells.
• Instead of manually typing references, reduce errors in formulas by clicking on the cell while entering a formula.
• Excel automatically selects related data for sorting but excludes headers.

Typing Data in Excel

• Click a cell, then type text; press Enter to move down or Tab to move right.
• Text overflows into adjacent empty cells, but it belongs only to the original cell.
• Click a cell, then type a number; numbers align to the right by default.
• If a number overlaps with text, check the formula bar to verify its contents.
• Excel formats recognizable dates automatically (e.g., 20/03 → March 20).
• Formulas can calculate dates (e.g., =B8 + 5 adds 5 days to cell B8).
• Check cell contents in the Formula Bar to see actual data.

Combining Data with Flash Fill

• Flash Fill automatically completes data patterns based on the first entry.
• Manually type the pattern in the first cell, select that, and use the Data tab → Flash Fill
• Flash Fill can be to extract specific data from a longer sequence.

Using Autofill for Sequences

• AutoFill detects patterns like numbers, months, and days.
• Type the first two items, select the cells, and drag the AutoFill handle.
• AutoFill completes the series automatically.
• To duplicate data, select multiple empty cells, type a value in the first cell, and press Ctrl + Enter.
• You can duplicate data by holding Ctrl, select multiple cells, and enter data.

FORMATTING NUMBERS, DATES, CURRENCY, AND TIME IN EXCEL

• Excel automatically applies formats to numbers, dates, and times, but you can customize them based on your needs.
• Formatting dates, currency, and numbers ensures clarity, consistency, and accuracy in data representation. -Number formatting changes how data appears but not its actual value.
• Apply data validation ensure correct data entries
• A short cut to open a data validation window is Ctrl + 1

DATA VALIDATION IN EXCEL

• Data Validation controls the type of data that can be entered into a cell, for example: Restricting values Limiting character length Allowing only specific date/time ranges Drop-down lists

Editing Worksheet Data

• Editing worksheet data involves changing the contents of a cell by clicking on the cell or typing in the formula bar.
• By double-clicking the cell, or pressing “F2” to edit the data
• Using a dark shaped active cell indicator makes it easier to edit a worksheet
• Use arrow keys to move the active cell.
• To scroll back to the active cell, simply press Ctrl + Backspace

Structuring your Worksheet

• Organize the worksheet so that columns and rows for data entry are easily identified.
• Hide particular rows and columns to improve its apperance.
• Frezze particular rows and column
• Splilting rows and columns

Freezing and Splitting columns and rows

• Freezing locks particular rows and columns while scrolling to keep the top row and first column visible.
• Splitting creates separate windows within the same worksheet.
• To freeze, select the cell below or to the right of the area to freeze, go to the View tab, and click Freeze Panes.
• To split, select a cell and click the Split button in View tab, and then drag the split bar.

Unfreeze and Unsplit Rows and Columns

• To unsplit, click the Split button once, double-click split bars, or drag the split bar to the worksheet edge.
• To unfreeze, click Freeze Panes under the View tab, and select Unfreeze Panes.

Hiding and Unhiding Rows and Columns

• Useful when working with large datasets to reduce clutter and focus on specific sections of data.

How to Hide Rows

• Select the row(s) to hide, right-click, and select Hide or press Ctrl + 9.

How to Unhide Rows

• Select the rows above and below the hidden row(s), right-click, and select Unhide or press Ctrl + Shift + 9.

Unhiding the First Row (Row 1)

• Type A1 in the cell selection box and press Enter, then go to Home → Format → Hide & Unhide → Unhide Rows or press Ctrl + Shift + 9.

How to Hide Columns

• Select the column(s) to hide, right-click, and select Hide or press Ctrl + 0 (zero).

How to Unhide Columns

• Select the columns on both sides of the hidden column, right-click, and select Unhide or press.

Unhiding the first Column (Column A)

• Type A1 in the cell selection box and press Enter.
• Go to Home → Format → Hide & Unhide → Unhide Columns.

Unhide All Rows and Columns at Once

• Select the entire worksheet by pressing Ctrl + A.
• Right-click on the row and column identifiers and select Unhide, or press Ctrl + Shift + 9.

Comments for Documenting Your Worksheet

• Comments attach notes to a cell to provide additional details, and are identified by a small red triangle in the top-right corner.
• Hovering over the triangle displays the comment in a pop-up box.

Inserting a Comment

• Inserting a Comment: Click on the cell, go to the Review tab, click New Comment, type in pop-up, and click outside..
• Hover the mouse pointer over the red triangle to view the comment.

Finding a Comment

• Navigate to the Review tab.
• Use the Previous or Next button to move between comments.

Editing a Comment

• Editing a Comment: Select cell, Review tab, click Edit Comment, modify in pop-up.
• Select all the cells containing comments.
• Delete Comments using the Review tab orusing Find & Select under the home tab.

Selecting Cells in a Worksheet

• Left-click on a cell to make it active. A thick box will surround the active cell.
• Hold down the Ctrl key and click on each cell you want to select. Multiple selected cells will become shaded to indicate selection.
• Method 1 (Using Shift Key) Click on the first cell in the range. Hold down the Shift key and click on the last cell in the range.
• Method 2 (Click and Drag) Click the first cell in the range. Hold the left mouse button and drag to the last cell in the range.
• Click the square area at the top-left of the worksheet (above row numbers and left of column letters). OR press Ctrl + A to select all cells.

Deleting Cell Contents

Select the cells to delete and press the Delete key on the keyboard. Select the cell(s) you want to move or copy. Right-click and choose Cut (to move) or Copy (to duplicate).

• Click on the destination cell, right-click, and select Paste.

Managing Worksheets

• Click on the worksheet tab at the bottom of the screen to switch sheets.
• Click and drag the worksheet tab to a new position. To select all worksheets, right-click on any worksheet tab and select "Select All Sheets".
• Select the worksheet → Go to the Home tab.
• Deleting a sheet will permanently remove it.
• Protecting worksheet prevents it from unauthorized users.

Computing Data with Formulas and Functions

• Excel formulas are essential for numerical computation and data analysis Referencing the Cells via Formulas Referencing Cells from Another Worksheet Formula errors are marked by a small green triangle in the upper-left corner of the cell.

Four Types of Excel Operators

• Arithmetic Operators - perform basic mathematical functions
• Reference Operator: gathers a range of cells and refers them for calculation.
• Comparison Operator - compares one value with another and establishes reasonable outcomes, either True or False
• Text concatenation type - joins one or more values together to produce a single piece of text

Inserting a Cell Range

• A cell is identified by column letter + row number (e.g., B2).
• A cell range is a group of adjacent or non-adjacent cells.

Applying the Same Formula to Multiple Cells

• Use the Auto-Fill (drag the plus icon) to apply the formula across rows or columns

Formulas

• Use Formula Bar → Define Name
• Use in Formula or go to Formula → Use in Formula and select the named range.
• Manage a Named Cell Range -Rename/Delete a Named Range: Go to Formulas → Name Manager. Select the range to edit. Click Edit to rename or Delete to remove it. Copying Formulas from One Cell to Another

Detecting and Fixing Formula Errors

#NAME - a range name that is not well defined such as adding certain symbols to the range name #NULL - Referring to a cell range that is not entered correctly #DIV - Wrongly divide the number by something that does not exist, like zero #REF - Referring to a range name that does not exist #VALUE - it occurs when formulas are being miswritten or you used incorrect functions #NUM - using unacceptable and incorrect argument

Using COUNT and COUNTIF to Count Data in a Cell Range

• COUNT Function - counts all numerical values in a range.
• COUNTIF Function - counts cells that match a condition

Joining Text with Values Using Concatenate

• alternative using Amper Sand, which a method simplest for joining text.
• ALTERNATIVE: Concat, is a newer version in excel.

Using Average for Averaging Point Value

-AVERAGEIF - Averages only values that meet a specific condition. AVERAGEIFS - Averages values based on multiple conditions.

• AVERAGEIFS - Averages values based on multiple conditions.

PMT Function for Estimating Loan Payments

• Annual Intrest rates, number of payment and the loaned amount affect how payments are estimated

###Project Time Measuring with Networkday and Today

• TODAY:No arguments needed. The function updates daily when the file is opened. Calculating the Total Number of Days Between Two Dates.
• Use the minus (-) operator to find the difference between two dates.
• Includes weekends in the calculation
• Functions: LEN, MIN, MAX, SMALL, LARGE. Len For Counting Text Character

###Text Capitalizing with Proper Function

• With the proper function, the text is changed into a proper case with first letter of each word capitalized and the rest in lower case. LEFT, MID, and RIGHT for Data Extraction
• LEFT Function: Extracts a specified number of characters from the beginning(left side) of a giving text.
• MID Function: Extracts characters from the middle of a giving text
• RIGHT Function: Extracts a specified number of characters from the end.
• Using Analytical Identification: IF Function
• Nested IF: The IF function can also be nested to evaluate multiple functions.