π Lecture 18 β Preparing Outputs
π Overview: This lecture explains how to review and proofread a document before finalizing it, including checking layout, spelling, and grammar. It also covers printing options and how to produce physical copies of documents effectively.
ποΈ Topics Covered
The lecture covers reviewing a document for layout, margins, numbering, headers, footers, and text settings. It details spelling and grammar check features in MS Word, including how to correct errors using the Review tab or right-click. Finally, it explains printing a document, selecting printers, page orientation, page sizes, and margins.
π Lecture Summary
Objectives
The objective of this lecture is to provide information about how to review and proofread a document, how to use a word processor to proofread, how to remove spelling and grammar mistakes, how to take printouts of a document, and how to make use of different printing options.
Reviewing a document
When a document is prepared, it is thoroughly checked from different perspectives. A document is checked for its layout and the information provided in it. Documents are also checked for spelling and grammar mistakes. We should review our document made with the help of a word processing application. A complete document review includes: complete page layout, page margins and numbering, header and footer settings, and text and paragraph settings.
A very important part of document review is spelling and grammar check. A word processor gives us a very important feature of spelling and grammar check. It indicates any spelling mistake in the document and points out any grammatical mistake as well. While writing in MS Word, spelling mistakes are shown by red lines and grammar mistakes are shown by green lines. Using the spelling and grammar check feature, we can remove mistakes from our document.
There are two methods to correct such mistakes in a document:
- We can use the spelling and grammar check feature. Go to the start of the document. Click the Review tab and click on the button highlighted in the figure. It will open a new dialogue box with correct options. You can do the correction, ignore it, or add it to the dictionary.
- We can use the right click of our mouse. Right-click on the word with a red line below it to see the available corrected options.
Words not included in the computer dictionary are highlighted as mistakes. For example, nouns like places, Muslim names, etc., are considered spelling mistakes. We can add such words to the dictionary so that they are not considered a mistake next time. Right-click on the highlighted word and select "Add to Dictionary".
The green wavy line under a sentence represents a grammatical mistake. The computer is suggesting you revise the fragment from a grammar perspective.
π‘ Why this matters: Mastering these proofreading tools ensures your document is error-free and professional, saving time compared to manual checking.
Printing a document
We can take prints of a document prepared in a word processor. The files created using software and saved in a computer are called a "soft copy". To take the print, you can do any of the following:
- Go to File < Print
- Press Ctrl + P
A dialogue box will open in either case. You will see different options available for print. You can select All, only current page, or a page range for prints. Multiple copies can be sent to the printer at a time of a document with a single click.
π Commands: File < Print or Ctrl + P β Opens the print dialogue box with all settings.
Select Printer: If more than one printer is attached, we can select a printer we want to print from.
Page Orientation: We can select the available page orientation: Landscape or Portrait.
Page Sizes and Margins: You can set page margins and select required page size, for example, A4, Letter, and Legal. Margins can also be chosen as Narrow, Normal, or other options.
β Key Takeaways
This lecture emphasizes that reviewing a document involves checking layout, margins, headers, footers, and text settings alongside spelling and grammar. The two main methods for correcting spelling and grammar errors are using the Review tab's spelling and grammar feature or right-clicking on a marked word. For printing, students must remember the shortcuts (File < Print or Ctrl + P) and key options including printer selection, page orientation (landscape/portrait), page sizes (A4, Letter, Legal), and margin settings. The distinction between a soft copy (digital file) and a physical printout is also fundamental.
π§ Quick Revision Questions
- What two colors indicate spelling and grammar mistakes in MS Word, and what does each color represent?
- Describe two different methods to correct a misspelled word in a Microsoft Word document.
- What is the keyboard shortcut to open the Print dialogue box?
- Name three different page sizes that can be selected while printing a document.
- What does it mean to "Add to Dictionary" when correcting a spelling error?
π Lecture 19 β Mail Merge
π Overview: This lecture introduces the Mail Merge feature in word processing software, which allows users to create personalized bulk documents like letters and envelopes. It explains the step-by-step process of preparing a main document, creating a mailing list, merging data, and printing the final output, making it essential for efficient mass communication.
ποΈ Topics Covered
The lecture covers the basic concepts of mail merge, its two main steps (preparing the document and the recipient list), creating and editing a mailing list using the Mail Merge Wizard, inserting merge fields, preparing mailing envelopes, and printing the merged documents for all recipients.
π Lecture Summary
Basic concepts
Mail merge is a feature that combines information (like names and addresses) from a list with a main document (like a letter) to create personalized copies for each recipient. This can be used for postal mail or electronic mail. The process involves three steps: 1) Prepare the letter or invitation, 2) Prepare the list of recipients, and 3) Merge the information from the list into the main letter. For example, a convocation letter is the same for all students, but the student's name and address are different for each copy.
π Definition β Mail Merge: A feature that merges a list of data (e.g., names, addresses) into a main document to produce multiple personalized copies.
1. To develop invitations using mail merge feature do the following steps
To start, click the Mailings Tab. Then, click Start Mail Merge and select "Step-by-Step Mail Merge Wizard" to open the Mail Merge pane on the left side.
- Select the βLettersβ radio button for the document type and click "Next: Starting document".
- Select βUse the current documentβ and click "Next: Select recipients".
- Select βType a new listβ and click Create... A new window will open with text fields (e.g., Name, Address). Enter information for each recipient, then click OK. You will be asked to save the list (e.g., save as "Students"). Finally, click "Next: Write your letter".
π‘ Why this matters: This wizard guides you through the entire process step-by-step, making it easy for beginners to set up a mail merge correctly.
Insert Merge Field
After writing your letter, you need to add placeholders for the personalized data. Click the Mailing tab, then click Insert Merge Field (available in the "Write and Insert Fields" group). Select the required field (e.g., "Name", "Address") from the list and click Insert. Once inserted, click Close.
π Definition β Insert Merge Field: A command that adds a placeholder (like Β«NameΒ» or Β«AddressΒ») into a document, which will be replaced with actual data from the mailing list when merged.
Edit Data List
To edit the list of recipients, click the Mail Recipients button in the Start Mail Merge group on the Mailing tab. A new window will open showing your list. Select any entry and click Edit to make changes. In the edit mode, you can delete, find, sort, and add new entries.
Mailing Envelopes
You can also create mailing envelopes using Mail Merge. Click the Envelopes option from the Mailings Tab (likely "Create" β "Envelopes") to adjust envelope size and settings. Then, click Select Recipients to choose your list (e.g., a previously saved list). After that, click Insert Merge Field to add fields like "Address" to the envelope. Finally, you can print these envelopes.
Printing a merged document
To print the final merged documents, click the βFinish and Mergeβ button in the Mailing tab. Then, click Print Documents. In the "Merge to Printer" window, select "All" to print for all records. Click OK. A new printer dialog will open; click OK again to send the print request.
β Key Takeaways
The most critical concept is that mail merge automates the creation of personalized bulk documents by combining a main document with a data list. You must remember the two core steps: creating the document with merge fields and preparing the recipient list. The Mail Merge Wizard (Letters β Start Document β Select Recipients β Write Letter) is the standard workflow. Insert Merge Field is the tool to add data placeholders, and you can edit the list at any time. Finally, Finish & Merge with "Print Documents" is the final step to output personalized copies for all recipients.
π§ Quick Revision Questions
- What are the two main components you need to start a mail merge?
- In the Mail Merge Wizard, after selecting the document type as "Letters", what is the next step you must complete?
- What is the function of the "Insert Merge Field" command?
- How can you edit a single recipient's information in an existing mailing list?
- What are the final steps to print all merged letters for all recipients?
π Lecture 20 β Starting Spreadsheet Software: Introduction to MS Excel Interface and Basics
π Overview: This lecture introduces Microsoft Excel as a popular spreadsheet software for numeric data calculation and formatting. It covers the Excel interface, workbook and worksheet concepts, cell operations, and basic formatting techniques essential for data management.
ποΈ Topics Covered
The lecture begins with the history and development of spreadsheet software from VisiCalc to modern Excel, then defines spreadsheets, workbooks, and interfaces. It explains the MS Excel interface components including the Ribbon, Quick Access Toolbar, Name Box, Formula Bar, rows, and columns. The lecture covers modifying the Ribbon, cell operations (inserting, deleting, clearing content), text formatting (wrap, merge, alignment), and number/date formatting options including Short Date, Long Date, Time, Percentage, and Fraction formats.
π Lecture Summary
Starting Spreadsheet Software
Microsoft Excel is popular and widely used spreadsheet software. To open Excel: Click on Start < All Programs < Microsoft Office < Microsoft Excel. Initially, a splash screen appears for a few seconds, then a new Excel file opens. The default arrangement of toolbars, panels, and sheets is called the default layout. The main purpose of the spreadsheet is to calculate numeric data and apply different formats on data.
Development of Spreadsheet Softwares
In 1961, Prof. Richard Mattessich gave the idea of Electronic Spreadsheets. In 1978, Dan Bricklin designed an interactive visible calculator program called VisiCalc, consisting of four columns and twenty rows. The main purpose of VisiCalc was to get user's data and manipulate it.
Different Spreadsheet Softwares available in the market include: Gnumeric, Kingsoft spreadsheets, Spreadsheet (DHTMLX), LOTUS 1-2-3 (Release 2.1), CA-SuperCalc, MultiPlan, Quattro Pro, and Microsoft Excel. Widely used software is Microsoft Excel.
What is Spreadsheet?
A spreadsheet is a collection of worksheets. It represents the accounting ledger's bookkeeping record format. In other words, a spreadsheet is a way of representing bookkeeping records of an accounting ledger.
What is Workbook?
A workbook is a collection of spreadsheets. An MS Excel file is called a workbook.
What is Interface?
The screen which appears when you first open MS Excel is known as an interface. MS Excel 2007 and MS Excel 2010 interfaces are similar.
There is a Quick Access Toolbar on the top of the MS Excel interface. Below the Quick Access bar there is a Ribbon bar. The Ribbon bar consists of different tabs: File, Home, Insert, Page Layout, Formulas, Data, Review, View. Below the ribbon bar there is a Name Box and Formula Bar. The Name Box represents a cell reference (cell location and number) and shows a cell address. The Formula Bar is used to edit or change data using formulas, text, or functions.
π Column: A group of different cells from Up-to-Down. π Row: A group of different cells from Right-to-Left.
There may be one or more worksheets in a workbook. Workbooks are also known as spreadsheets. By default, there are three spreadsheets in a workbook.
About Worksheets
You can scroll worksheets and also Edit, Delete, or add a worksheet. You can view a worksheet in three different ways: Normal view, Page Layout View, and Page Break Preview (for overview). By default, a workbook opens in Normal view.
How to Modify the Ribbon or Quick Access Toolbar
- Right-click on the Ribbon; a pop-up menu will open.
- Go to Customize the Ribbon option and click.
- A new Excel Option dialogue box will open.
- Click on the New tab button.
- Add or remove desired commands by checking/unchecking checkboxes, then click OK.
π Compatibility View: Enables all old features and disables all new/advanced features of MS Excel. MS Excel 2010 file format is .XLSX.
Cells and Cell Operations
π Cell: The fundamental unit of an MS Excel worksheet. A cell is a rectangular box or an intersection point of a row and column. In cells, Text, Formatting, Attributes, Formulas, and Functions are inserted as data.
How to Delete Cell Content:
- Click Home tab > Editing group > Clear > select Clear Content from the dropdown.
- OR select cell and press Backspace.
- To delete contents from multiple cells: Select cells and press Delete button.
Note: There is a huge difference between cell deletion and cell content deletion.
To Delete Cells from Spreadsheet:
- Select cells to delete.
- Click Home tab > Delete.
- Select the row if you want to insert data below the row.
- Select the next column if you want to insert a column before the selected column.
Formatting Text and Cells
A formatted text enhances readability.
π Text Wrap: Used to view complete cell content. It means cell content becomes easily available by selecting the Wrap Text option. Wrap Text exists under the Home tab.
Merge and Center Cells:
- Select cells to merge and center.
- Click on Merge and Center dropdown list under Home tab.
- Select Merge and Center option.
How to Change Text, Cell Color, and Style:
- Select cells, then go to the A^ button under Home tab to grow font size.
- Select cells, then go to the Av button under Home tab to shrink font size.
Alignment Defaults:
- By default, Numbers are bottom right aligned in a cell.
- By default, Text is bottom left aligned in a cell.
- By default, each cell is set as a General format cell setting.
Number and Date Formatting
Numbers are written in decimal format. Currency and decimal are specially aligned right and left respectively.
π Short Date: e.g., 22/03/2017 π Long Date: e.g., Wednesday, April 22, 2017 π Time: hr:mm:ss π Percentage: Data is written with percentage sign, e.g., 400.00% π Fraction: Data is written with forward slash, e.g., ΒΌ will be written as ΒΌ in MS Excel.
Note: In General format, ΒΌ will be written as 4-Jan.
By default, Excel converts any large number value into scientific notation. By default, Excel is set in Text format. The More Number Formats option is used to convert currencies (e.g., dollars into PKR). Path: Home < Number < More Number Formats Option.
π‘ Why this matters: Understanding these formatting options is essential for professional data presentation and accurate numerical representation in spreadsheets.
β Key Takeaways
The fundamental building blocks of Excel are workbooks (files containing multiple worksheets), worksheets, rows, columns, and cells. The interface consists of the Quick Access Toolbar, Ribbon with tabs, Name Box, and Formula Bar. Cell operations include content deletion (using Clear or Delete key) versus cell deletion (removing cells entirely, shifting adjacent cells). Text and number formatting options include wrap text, merge and center, font size changes, alignment defaults (numbers right, text left), and special formats like Short Date, Long Date, Time, Percentage, and Fraction. Understanding these basics enables efficient data entry, manipulation, and professional formatting in Excel.
π§ Quick Revision Questions
- What is the difference between a workbook and a worksheet in MS Excel?
- How do you modify the Ribbon or Quick Access Toolbar, and what is Compatibility View?
- What is the difference between deleting cell content and deleting cells themselves?
- What are the default alignment settings for numbers versus text in a cell?
- How do you apply Short Date format, and what happens if you type ΒΌ in General format?
π Lecture 21 β Saving an Excel File
π Overview: This lecture covers essential file management and workbook manipulation techniques in MS Excel, including how to properly save files in different formats (including PDF and compatibility mode), create and edit basic mathematical formulas, manage worksheet tabs through renaming, coloring, grouping, and moving, and finally how to prepare a worksheet for printing by setting page orientation, margins, breaks, and freeze panes. Mastering these skills is fundamental for efficient data entry, calculation, and professional document output.
ποΈ Topics Covered
The lecture begins with an explanation of the Save and Save As commands and how to save a compatible Excel 97-2003 file and a PDF. It then introduces creating simple formulas using standard mathematical operations with cell references. Next, it explains how to manage worksheet tabs by renaming, deleting, moving, copying, and coloring them, as well as grouping and ungrouping sheets. The lecture concludes with instructions on freezing panes and setting up a worksheet for printing, including page orientation, scaling, margins, and page breaks.
π Lecture Summary
Save As
Save As command is used when you are saving your file (Excel file) for the first time or when you want to save your excel file with another version of the excel file. For example, if you want to save your excel file created in MS Excel 2017 into MS Excel file 2003, the Save As command is used. When you save any file with a Save As command, your original file will also remain at its original location. By default, your file is saved in My Document folder.
Save
Save command is used to save your workbook on the current location. To recover your saved file, go to File < Info < Version, then click on the Restore button from the popup menu. By default, Excel auto saves an Excel file after every ten minutes. A file will not auto save if you close your excel file before 10 minutes.
Compatibility Mode
Compatibility Mode disables all new and advance features and enables only those basic features which are used to make only a workbook. Note: Old version of excel can only be opened in an advance excel in its compatibility mode only.
Save as a PDF
To save your workbook in Adobe Acrobat format is called to save as a PDF file.
Purpose to save as a PDF:
- Helps reader to read who do not have MS Excel
- Just Read your Workbook as a PDF file
- Reader cannot edit your workbook
How save a file as a PDF: File < Save As, select PDF from the drop down list of file type. By default, Excel only converts that worksheet in PDF on which you are working currently.
To convert whole workbook or all worksheets in PDF: Go to File < Save as, then click on Option button and select entire book option and then ok.
Creating Simple Formulas
What is Formula? Mathematical equation which is used to perform different mathematical calculations is called a formula, i.e. similar to a calculator that is used to perform different calculations.
Standard Mathematical Operations:
- + Plus: For Addition
- - Minus: For Subtraction
- ***** Asterisk: For Multiplication
- / Forward Slash: For Division
- ^ Caret: For Exponent
In MS Excel all formulas are applied with an equivalent sign (=). Note: Excel will never tell you that either your formula is right or wrong.
To create a simple formula in Excel: Select the cell in which you want to show your result, then enter value in formula bar.
Creating Formula with cell reference: Select the cell in which you want to show your result, then first write the cell name and secondly its value in the formula bar, and in last press enter. For example, if you want to select Column C and Row 3, then write C3 in the formula bar.
To create a formula with point and click method: Select the cell in which you want to show result, then enter formula in it. For example, =C3*C4.
How to Edit a Formula? Double click on the cell OR first select cell and then edit (or change) it in formula bar.
Introduction to worksheets
By default, three worksheet tabs named as sheet1, sheet2, and sheet3 are displayed on the bottom left side of the screen when you open MS Excel for the first time. You can copy, delete, move, insert, rename, and also give colors to the spreadsheets.
Rename Worksheets
Right click on the current (selected) worksheet, then go to Rename and rename it.
To Delete Multiple sheets from the spreadsheet: Press Shift tab and select different sheets which you want to delete, then right click on any selected sheet and finally click on Delete.
Move or copy worksheet: Right click on the sheet which you want to copy or move. Go to Move and Copy option, then check the Create a copy checkbox, and finally click on OK button. Result: a new same worksheet with an updated version will be created.
Move a spreadsheet: Drag the worksheet and then drop the spreadsheet when a dropdown arrow (v) appears on the worksheet tab.
To color a worksheet: To color a worksheet means to give color to the worksheet tab. Right click on worksheet tab, then go to Tab Color option, select color of your own choice, and finally click on OK button.
Grouping and Ungrouping worksheets
Press Ctrl key from the keyboard and click on all those worksheet tabs which you want to group.
Ungrouping the worksheets: Right click on worksheet tab, then click on Ungroup option.
Freezing worksheet panes
You can freeze one or more sheets in Excel; this is called Freezing Panes. Freezing is very helpful when you scroll your worksheet.
How to freeze a row? Select the next row to the row which you want to freeze. Go to View tab (situated on the Ribbon Bar). After clicking on the view tab, select Freeze Panes option from the freezing command dropdown list.
How to print a worksheet on a paper
Press Ctrl+F2 to view page print preview. Note: Print preview option has been removed from MS Excel 2010.
Click on File tab from the Ribbon bar; a back stage view will open. Click on Print option from the Back stage view. Different printing options (e.g., Print, Printer, and Setting) will appear on the left side of the opening window, and Page Preview will be displayed on the right side.
A document or worksheet can be printed in two different layout styles:
- Landscape Style: In Landscape style, a worksheet is horizontally set.
- Portrait Style: In Portrait Style, a worksheet is vertically set.
It is better to print in Landscape when large numbers of columns need to be printed on the paper.
How to reduce worksheet size?
Click on File tab, then click on Print option. On the left side under settings, click on No Scaling dropdown list. Then click on Fit sheet on one page, and finally click on OK button.
Margin
Margin: Printed worksheet border content or distance from the text is known as the margin.
To use print titles:
- Click on Page Layout tab from the Ribbon bar, select Print Titles command from the Page Setup group of commands.
- A Page Setup dialogue box will open.
- Enter row value in the Rows to repeat at top textbox.
How to break a worksheet?
Select the column from where you want to break the spreadsheet. Then click on Page Layout tab from the Ribbon bar. Go to Page Setup group of commands and click on Breaks dropdown list, then click on Insert Page Break option to insert the break.
β Key Takeaways
The most critical concepts to remember are: the fundamental difference between Save (updates the current file) and Save As (creates a new file or new version), and that all Excel formulas must begin with an equals sign (=). You must know the standard mathematical operators (+, -, *, /, ^) and how to use cell references (e.g., C3) in formulas to make them dynamic. For worksheet management, remember that you can right-click a tab to rename, color, move/copy, or delete it, and you can use Ctrl+click to group multiple sheets for simultaneous editing. Finally, for printing, know how to change page orientation between Portrait and Landscape, use Fit Sheet on One Page to scale a worksheet, and insert page breaks to control where a new page starts.
π§ Quick Revision Questions
- What is the key difference between using the Save command and the Save As command in Excel?
- If you want to create a formula to add the values in cell A1 and cell B1, what would you type into a third cell?
- Describe the exact steps to give a different color to a worksheet tab.
- Why would you use the Freeze Panes feature, and which row should you select before activating it if you want to freeze the top three rows (rows 1, 2, and 3)?
- How do you ensure that all sheets in a workbook are converted to a single PDF file instead of just the active sheet?
π Lecture 22 β Creating Complex Formulas
π Overview: This lecture teaches how to create and manage complex formulas in Microsoft Excel, focusing on the order of operations, cell referencing types, and the use of built-in functions. Understanding these concepts is essential for accurate data analysis and efficient spreadsheet management.
ποΈ Topics Covered
The lecture covers writing complex formulas with multiple mathematical operations, the importance of the order of operations (PEMDAS), the difference between relative and absolute cell references (using $ and F4 key), the syntax and arguments of functions like SUM, AVERAGE, COUNT, and PRODUCT, and practical use of the AutoSum dropdown list. It also introduces the NETWORKDAYS function for calculating working days.
π Lecture Summary
About Complex Formulas & Order of Operations
Complex formulas contain more than one mathematical operator or function. The order of operations is critical for correct calculation. The sequence is: operations within parentheses '(' ')' first, then the caret '^' for exponentiation, then multiplication and division, and finally addition and subtraction.
π Definition β Order of Operations: The sequence in which mathematical operations are performed in a formula.
π Formula: ( ) β ^ β * , / β + , - β Plain-English: "Parentheses first, then exponents, then multiplication and division, lastly addition and subtraction."
π Example: In the formula = (5+3)*2, the addition inside parentheses (5+3=8) is performed first, then the multiplication 8*2=16. If written as =5+3*2, multiplication would be done first (3*2=6), then addition (5+6=11).
Working with Cell Reference and Absolute Reference
A cell reference is typically relative, meaning it changes when copied. An absolute reference uses a $ sign (e.g., $A$1) and remains fixed when copied. Pressing the F4 key toggles a cell reference to absolute.
π Definition β Relative Cell Reference: A standard cell reference (e.g., A1) that adjusts automatically when the formula is copied to another cell.
π Definition β Absolute Cell Reference: A cell reference with a dollar sign $ before the column letter and/or row number (e.g., $A$1) that does not change when the formula is copied.
π Formula: Toggle to absolute by pressing F4 after selecting a cell reference in a formula.
π Example: If cell B1 contains =A1*$C$1 and is copied from B1 to B2, the relative reference A1 becomes A2, but the absolute reference $C$1 remains $C$1. This is useful for multiplying multiple numbers by a fixed value like a tax rate.
Functions: Syntax and Common Examples
A function is a predefined formula for calculation. Each function has a specific order or syntax. Common functions include SUM, AVERAGE, PRODUCT, and COUNT. The syntax includes an equal sign, the function name, and arguments inside parentheses.
π Definition β Function: A predefined formula that performs a specific calculation.
π Definition β Syntax: The specific order and structure required to write a function correctly.
π Syntax: =FunctionName(Arguments) Example: =SUM(B3:B12) β = + SUM + (B3:B12)
π Arguments Detail: Arguments are enclosed in parentheses. A colon : indicates a cell range reference (e.g., C1:C5). A comma , separates individual values or non-adjacent cell references (e.g., =COUNT(C1:C5, C7:C10, C11:C15)).
β οΈ Note: Excel does not tell you whether your function is logically true or false; it only shows the result.
Using Common Functions to Represent AutoSum
The AutoSum feature provides quick access to common functions. Select the result cell, go to the Formula tab on the Ribbon, click the AutoSum dropdown in the Function Library group, and choose an option like Sum, Average, Count Numbers, Max, or Min. Then select the cells to include and press Enter.
π Definition β AutoSum: A button on the Formula tab that lets you quickly insert functions like Sum, Average, Count Numbers, Max, and Min.
π Example: To find the average of values in cells A1 to A10, select cell A11, go to Formula tab > AutoSum dropdown > Average, select A1:A10, and press Enter.
Additional Functions: Date & Time and NETWORKDAYS
The Function Library under the Formula tab contains groups like Math & Trig. The More Functions category includes Statistical, Engineering, Cube, and Compatibility functions. Excel also includes useful Date & Time functions. The NETWORKDAYS function calculates the number of working days between two dates.
π Definition β NETWORKDAYS Function: A Date & Time function that calculates the number of whole working days between two dates, automatically excluding weekends.
π Syntax: =NETWORKDAYS(Start_date, End_date)
π How to use:
- Select the result cell.
- Go to the Formula tab on the Ribbon.
- Click
Date & Timedropdown in the Function Library group. - Select
NETWORKDAYS. - Enter the start and end dates as arguments and press Enter. π‘ Why this matters: This function is vital for calculating project timelines, payroll hours, or days until a deadline, automatically skipping Saturday and Sunday.
β Key Takeaways
The order of operations (Parentheses, Exponents, Multiplication/Division, Addition/Subtraction) must be strictly followed for correct complex formula results. Mastering the difference between relative cell references (which change when copied) and absolute cell references (fixed with $ sign, toggled by F4) is crucial for formula scalability. Functions are predefined formulas with a specific syntax (=FunctionName(Arguments)) where a colon : defines a range and a comma , separates individual arguments. The AutoSum dropdown on the Formula tab provides quick access to common functions like SUM, AVERAGE, COUNT, MAX, and MIN. Finally, the NETWORKDAYS function is a practical Date & Time tool for counting working days between two dates.
π§ Quick Revision Questions
- List the four steps of the order of operations for complex formulas in Excel, in the correct sequence.
- What is the difference between a relative cell reference (e.g., A1) and an absolute cell reference (e.g., $A$1)? Which key toggles between them?
- In the function
=SUM(A1:A10, C1), what do the colon:and the comma,represent? - Where do you find the AutoSum dropdown list, and name at least three functions available in it.
- Which function calculates the number of working days between two dates, and which tab/group on the Ribbon contains it?