FOC001-eL7

IDOL Institute of Distance and Online Learning ENHANCE YOUR QUALIFICATION, ADVANCE YOUR CAREER.

BA/B.Com/BBA/B.Sci .TTM 2 All right are reserved with CU-IDOL COMPUTER FUNDAMENTALS ❑Course Code: BBA104/BCM104/BTT104/BAQ102 ❑Semester: First ❑e-Lesson: 7 ❑SLM Unit: 9 www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102)

COMPUTER FUNDAMENTALS 33 INTRODUCTION OBJECTIVES • Examine the value of using Excel to make decisions. To develop an intuitive sense of how • Learn how to start Excel. computer work and how they can be used to • Become familiar with the Excel workbook. make work more efficient. • Understand how to navigate worksheets. Students will be able to create technical and • Examine the Excel Ribbon. complex spreadsheets for data analysis. • Examine the right-click menu options. Understanding of introductory formatting • Learn how to save workbooks. techniques and presentation styles. • Examine the Status Bar. • Become familiar with the features in the Excel Help window. www.cuidol.in Unit-9(BCM104/BBA10140/B4T/BTT1T0140/B4/ABQA1Q0120)2 ) INSTITUTE OFADllISrTiAghNtCaErAeNreDsOerNvLeIdNEwLitEhACRNUI-NIDGOL

TOPICS TO BE COVERED 4 > Excel: Introduction Advanced > Workbook and Worksheet > formatting in excel and formatting in Excel > Working with formulas, www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Saving your Workbook 5 • To save your document, simply click on the MS 2013 logo in the top left‐hand corner [ ] and the menu bar that you see on your right here will drop down giving you various options, including saving. https://en.wikipedia.org/wiki/Microsoft_Excel www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Saving your Workbook cont’d 6 When you save a workbook in MS Excel 2013, it automatically saves with “.xlsx” as its extension. https://www.howtoexcel.org/tutorials/saving-your-workbook/ www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Some Terminology 7 • Workbook – An Excel file containing several worksheets • Worksheet – Rectangular grid of rows & columns that labels and values are inserted into • Cell ‐ The intersection of a row and column, identified by an address (ex. A1, F4, Z55) • Value – Numerical data in a cell • Label – Non‐numerical data in a cell • Table – A logically distinct group of cells, visually distinguished with borders and shading www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Some Terminology 8 www.cuidol.in https://www.computerhope.com/jargon/w/workbook.htm All right are reserved with CU-IDOL Unit-9(BCM104/BBA104/BTT104/BAQ102)

Cell Referencing 9 Column(Letter) then Row(Number) https://www.educba.com/merge-cells-in-excel/ www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Navigation Shortcuts 10 In addition to the intuitive mouse and arrow key movement between cells, these shortcuts can make lots of data easier to input: • Home : Move to beginning of row • Ctrl+Home : Move to “A1” • Enter : Move one cell down • TAB : Move one cell to the right • Shift+TAB : Move one cell to the left • End+ : Move to last used cell in row • End+ : Move to last used cell in column • F2 : Edit the the content of a cell www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Renaming Worksheets 11 ” Right‐click on the tab for the sheet you need to rename and choose Rename • Notice the other options that are available– We can also insert, delete,copy, and select all sheets • Rename the sheet to “My First Spreadsheet https://www.top-password.com/blog/rename-one-or-multiple- worksheet-tabs-in-excel/ www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Entering Data 12 • To enter simple data into a cell we can either: • – Position the cursor on the cell • and then type on the keyboard and press Enter • OR -Position the cursor on a cell and then click on the formula bar • , type the data, press Enter or click on the to accept or on the to cancel . www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Inserting New Rows and Columns 13 • Click on the row or column header • Right click and choose insert • Rows are inserted above the selected row • Columns are inserted to the left of the selected Column Shortcut keys Ctr+ + is used for insert row and column www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Deleting Rows and Columns 14 An Example… • Type a number into any cell in row “8” • Right‐click on a row header “8” & select Delete • Type a number into any cell in column “B” • Right‐click on the column letter “B”, & select Delete - The row/column is removed, along with any formulas, data and formatting. – The adjacent cells are shifted up/left and the row or column is re‐labeled to reflect the new change Shortcut keys Ctr+ - is used for delete row and column www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Column Width 15 • Make the first column, A, wide enough to accommodate the width of the text labels. • Right‐click on the column letter and you’ll be given the following options: • Select “Column Width” • Shortcut, you can also click on the line the column letters and drag cursor left or right. https://en.wikipedia.org/wiki/Microsoft_Excel www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Merging Cells 16 • Merging allows one cell to take up multiple rows and/or columns • To merge 2 or more cells, highlight the desired cells to merge and click on the merge & center button: https://www.google.com/url?sa=i&source=images&cd=&ved=2ahUKEwjU8KvWpqjmAhVkyzgGHRweBXkQjRx6BAgBEAQ&url=https% 3A%2F%2Fwww.educba.com%2Fmerge-cells-in-excel%2F&psig=AOvVaw1-rW38wLUDcgFhE3dtkYwN&ust=1575971820106403 www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Merging Cells cont… 17 https://www.google.com/url?sa=i&source=images&cd=&ved=2ahUKEwjU8KvWpqjmAhVkyzgGHRweBXkQjRx6BAgBEAQ&url=https% 3A%2F%2Fwww.educba.com%2Fmerge-cells-in-excel%2F&psig=AOvVaw1-rW38wLUDcgFhE3dtkYwN&ust=1575971820106403 www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Currency Style 18 • We can change the format of numerical data to appear as currency. • Highlight some columns which contain numbers • Click the Currency button on the Home tab ✓ Note: there are also buttons for Percentage Style , and Comma Style • Click on the Decrease Decimal button twice to remove decimal places https://en.wikipedia.org/wiki/Microsoft_Excel www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

19 https://www.exceldome.com/solutions/add-insert-an-excel-worksheet-after-a-specific-sheet/ www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

USES OF EXCEL 20 • Analysing and storing data • Excel tools make your work easier • Mathematical formulas of MS Excel make things easier • Uses of Microsoft Excel in Education • Uses of Microsoft Excel in Business • Uses of Excel in Daily Progress Report • Uses of Excel in Accounting www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Using Formulae 21 • A formula is a special entry in a cell, that calculates its value based on other cells, and/or constants • By beginning an entry into a cell with an “=” we let Excel know we’re using a formula • Without the equal sign “=” Microsoft Excel assumes you’ve entered a label or value www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

A Simple Formula 22 ❑Select any cell on your spreadsheet and type =800+100*2 ❑ Press <Enter> ❑ Excel recognizes the ‘=’ sign and calculates the result ❑ Note that the formula is shown in the formula bar (while that cell is selected) and that the computed value is displayed in the cell ❑ Also note that Excel respects the order of operations (BODMAS) https://en.wikipedia.org/wiki/Microsoft_Excel www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Linking Cells 23 • You can refer to cells in a formula by simply using the cell’s reference name. • Enter some data or a label into cell A1 • Select cell A2 and type: =A1 • Now, when the information in A1 is changed, those changes will automatically show up in cell A2 • You can even link cells from different worksheets! • For example, in Sheet2 select cell A1 and type: =My First Spreadsheet!A1 =sheet name !cell address *Note the Exclamation Mark!!! www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Using Cell Reference In Formulas 24 ❖ In Cell A2 type: =A1*2 ❖Now, change the value in A1 ❖ Excel will automatically update the value shown in cell A2 ❖So, this is just like a link ❖ the value in A1 is substituted in before Excel performs the calculations in cell A2 ❖ This works with cells linked in between different worksheets too! ☺ www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Built‐In Functions 25 Excel comes with hundreds of built‐in functions which can be sed in your formulas. • However, the majority of functions need data in order to be useful – For example, in order to use the Sum function, you need to tell Excel which values to sum up. Built‐In Functions are structured like this FunctionName (Parameter1,…) • The Function Name always comes first and usually describes what the function does. www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Built‐In Functions cont… 26 The Parameter is the information that the function needs in order to work • This can be a single value or a set of values and can be represented by either numbers (ex. 1, 2, 3, 5, 8, 13, etc…), cell references (ex. A1, B2, C3:D4, etc…) or text. www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Built‐In Functions cont… 27 There are two different ways to use functions: 1. The first way is to simply write them out ➢ This will save you time if you know what functions you’ll be using and how to use them 2. The second way is to use the wizard ➢ This is a great way to learn about the many different kinds of functions and operations that excel can perform and how to use them. =SUM(A1,A5,A10,A15,A20) • This formula uses commas and will sum up the values for the five cells A1, A5, A10, A15, A20 =SUM(A1:A20) • This formula uses a colon and will sum up the values for the range of cells A1 to A20. www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Built‐In Functions cont… 28 Let’s do an example! • Type numbers into the first 5 rows of column A • In cell A6 type: =SUM(A1:A5) • A6 now contains the sum of A1 to A5 • To find the average you would use: =AVERAGE(A1:A5) • How could you find the difference between the biggest number and the smallest number in a range of cells =MAX(A1:A5) – MIN(A1:A5) https://www.wallstreetmojo.com/alternatives-to-vlookup/ www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Sorting 29 To arrange data systematically in groups and to separate according to type. THREE TYPES OF SORTING • A to Z • Z to A • Custom www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Sorting 30 Sorting can be done from the Home tab by clicking on the “Sort & Filter” option. Choose from… – Sort Descending / Sort A to Z – Sort Ascending / Sort Z to A • Sorting can also be done from the Data tab by using the sort buttons • To Sort, just highlight the specific cells that you’d like sorted and then choose to have them sorted in Ascending (Z to A) or Descending (A to Z) order. www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Sorting cont… 31 https://www.wallstreetmojo.com/alternatives-to-vlookup/ www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Sorting cont… 32 • As can clearly be seen here, this table has now been sorted on three different level☺ www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

IF function 33 The Microsoft Excel IF function returns one value if the condition is TRUE, or another value if the condition is FALSE. The IF function is a built-in function in Excel that is categorized as a Logical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the IF function can be entered as part of a formula in a cell of a worksheet. Syntax:- IF( condition, [value_if_true], [value_if_false] ) Condition The value that you want to test. value_if_true Optional. It is the value that is returned if condition evaluates to TRUE. value_if_false Optional. It is the value that is return if condition evaluates to FALSE. www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

IF function example 34 Based on the Excel spreadsheet above, the following IF examples would return: =IF(A1>10, \"Larger\", \"Smaller\") Result: \"Larger“ =IF(A1=20, \"Equal\", \"Not Equal\") Result: \"Not Equal“ =IF(A2=\"Tech on the Net\", 12, 0) Result: 12 https://www.wallstreetmojo.com/nested-if-excel-multiple/ www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Filter 35 By filtering information in a worksheet, you can find values quickly. You can filter on one or more columns of data. You can filter based on choices you make from a list, or you can create specific filters to focus on exactly the data that you want to see. Filter your Excel data if you only want to display records that meet certain criteria. 1. Click any single cell inside a data set. 2. On the Data tab, click Filter. Shortcut key of filter is:- ctr+shift+l www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Filter cont… 36 3. Create a table. Make sure that your data have column headings to specify the data below it. https://www.excel-easy.com/functions/lookup-reference-functions.html www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Filter cont… 37 4. Click the arrow next to Country. 5. Click on Select All to clear all the check boxes, and click the check box next to USA in fig.1. 6. Click OK. Result. Excel only displays the sales in the USA fig 2. https://www.excel-easy.com/functions/lookup-reference-functions.html www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Filter cont… 38 6. To remove the filter, on the Data tab, click Clear. To remove the filter and the arrows, click Filter. Screenshot from excel www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Autofill 39 The Excel Autofill feature can be used to populate a range of cells with either a repeat value, or with a series of numeric values (e.g., 1, 2, 3, ...). To use the simple Excel Autofill: 1.Enter a value into the start cell; 2.Use the mouse to drag the 'fill handle' (the small black square at the bottom right of the start cell) across the range of cells to be filled; 3.When you drag the 'fill handle' across the range of cells to be filled, Excel will fill the selected cells, by either repeating the value in the first cell or by inserting a sequence from the first cell value (e.g. 1, 2, 3, ...); Screenshot from excel www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Autofill cont… 40 Click on the 'Auto Fill Options' box, which will appear at the end of your selected range of cells. This will give you the following different options: 1. Copy Cells - copy the initial cell across the selected range; 2. Fill Series - fill the selected range with a series of values (typically incrementing by 1), starting with the initial cell value; 3. Fill Formatting Only - fill the selected range with the formatting, but not the values of the initial cell; 4. Fill Without Formatting - fill the selected range with values, but do not copy the formatting from the initial cell. www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Pivot Table 41 A pivot table is a tool that allows you to quickly summarize and analyze data in your spreadsheet. You can use a pivot table when: •You want to arrange and summarize your data. •The data in your spreadsheet is too large and complex to analyze in its original format. If you want to follow along with this tutorial, download the example spreadsheet. https://www.excel-easy.com/data-analysis/pivot-tables.html www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

Multiple Choice Questions 42 1. Which of the following methods cannot be used to enter data in a cell a. Pressing an arrow key b. Pressing the Tab key c. Pressing the Esc key d. Clicking on the formula bar 2. Which of the following will not cut information? a. Pressing Ctrl + C b. Selecting Edit>Cut from the menu c. Clicking the Cut button on the standard d. Pressing Ctrl+X 3. Which of the following is not a way to complete a cell entry? a. Pressing enter b. Pressing any arrow key on the keyboard c. Clicking the Enter button on the Formula bar d. Pressing spacebar www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

SUMMARY 43 • Excel is a powerful tool for processing data for the purposes of making decisions. • You can find Excel commands throughout the tabs in the Ribbon. • You can customize the Quick Access Toolbar by adding commands you frequently use. • You can add or remove the information that is displayed on the Status Bar. • The Help window provides you with extensive information about Excel. www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

FREQUENTLY ASKED QUESTION 44 Question1. What is Microsoft Excel? Answer: Microsoft Excel is an electronic spreadsheet application that enables users to store, organize, calculate and manipulate the data with formulas using a spreadsheet system broken up by rows and columns. Question2. How many data formats are available in Excel? Name some of them. Answer: Eleven data formats are available in Microsoft Excel for data Storage. Example: Number – Stores data as a number Currency – Stores data in the form of currency Date – Data is stored as dates Percentage – Stores numbers as a percentage Text Formats – Stores data as string of texts Question3: How can you wrap the text within a cell? Answer: You must select the text you want to wrap, and then click wrap text from the home tab and you can wrap the text within a cell. www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

REFRENCES 45 1. Computer Fundamentals ,Anita Goel,Pearson Education India, 2010 - Computers - 500 pages 2. Introduction to Computer Applications-Pearson, New Delhi 3. file:///D:/computer/PPTS/Lesson%201%20(1).pdf 4. http://ecomputernotes.com/fundamental/introduction-to-computer/draw-the-block-diagram-of- computer-and-explain-its-various-components 5. http://www.chtips.com/computer-fundamentals/block-diagram-of-computer-system 6. http://wikieducator.org/Block_diagram_of_computer www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL

46 THANK YOU www.cuidol.in Unit-9(BCM104/BBA104/BTT104/BAQ102) All right are reserved with CU-IDOL