Microsoft Excel Tutorial PDF

ITS12613/ITS12513 – ESSENTIAL SKILLS IN DIGITAL PRESENTATION  Core Common Course 3 credit Lecture: Monday 8:00 – 10:00 AM Module Practical: 2 hours per week Information Assignment No. 01 - 02-12-2024 - 06-12-2024 Sheet As...

ITS12613/ITS12513 – ESSENTIAL SKILLS IN DIGITAL PRESENTATION  Core Common Course 3 credit Lecture: Monday 8:00 – 10:00 AM Module Practical: 2 hours per week Information Assignment No. 01 - 02-12-2024 - 06-12-2024 Sheet Assignment No. 02 – 06-01-2025 – 10-01-2025 40 marks for two assignments and 60 marks for the end-semester examination LMS Enrollment Key සිංහල මාධ්‍ය පාඨමාලාව - IT2025 English medium course - ITE2025  ITS12613 – Essential Skills in Digital Presentation SPREADSHEET APPLICATION USING MICROSOFT EXCEL Introduction to MS Excel Session 1 Department of Information Technology Faculty of Humanities and Social Sciences University of Ruhuna What is Spreadsheet? A spreadsheet is a computer application made up of rows and columns that help to calculate, organize, analyze and store data in tabular form. Example: Google Sheets (online and free) iWork Numbers - (free for Apple users) LibreOffice -> Calc (free) Lotus 1-2-3 Microsoft Excel Microsoft Office Excel Online (free) Gnumeric (free)  MS-EXCEL is a part of Microsoft Office suite software. Itis an electronic spreadsheet with numerous rows and columns, used for What is MS organizing data, graphically Excel? representing data(s), and performing different calculations. It consists of 1048576 rows and 16384 columns in Excel 2007 and later versions, a row and column together make a cell.  Microsoft Excel is a software application designed for creating What is MS tables to input and organize data. Excel It provides a user-friendly way to analyze and work with data.  Why Use Excel? It is the most popular spreadsheet program in the world It is easy to learn and to get started The skill ceiling is high, which means that you can do more advanced things as you become better It can be used with both work and in everyday life, such as to create a family budget It has a huge community support Templates and frameworks can be reused by yourself and others, lowering creation costs A visual representation of what an Excel spreadsheet What is a Cell? A spreadsheet takes the shape of a table, consisting of rows and columns. A cell is created at the intersection point where rows and columns meet, forming a rectangular box. Cell in a MS Excel Features of MS Excel Ribbon The Ribbon in MS-Excel is the topmost row of tabs that provide the user with different facilities/functionalities. These tabs are: Features of MS Excel Cont… Home Tab - It provides the basic facilities like changing the font, size of text, editing the cells in the spreadsheet, autosum, etc. Features of MS Excel Cont… Insert Tab - It provides the facilities like inserting tables, pivot tables, images, clip art, charts, links, etc. Features of MS Excel Cont… Pagelayout - It provides all the facilities related to the spreadsheet- like margins, orientation, height, width, background etc. The worksheet appearance will be the same in the hard copy as well. Features of MS Excel Cont… Formulas- It is a package of different in-built formulas/functions which can be used by user just by selecting the cell or range of cells for values. Features of MS Excel Cont… Data - The Data Tab helps to perform different operations on a vast set of data like analysis through what-if analysis tools and many other data analysis tools, removing duplicate data, transpose the row and column, etc. It also helps to access data(s) from different sources as well, such as from Ms- Access, from web, etc. Features of MS Excel Cont… Review - This tab provides the facility of thesaurus, checking spellings, translating the text, and helps to protect and share the worksheet and workbook. Features of MS Excel Cont… View- It contains the commands to manage the view of the workbook, show/hide ruler, gridlines, etc, freezing panes, and adding macros.  A collection of worksheets is referred to as a workbook (spreadsheet). Workbooks are your Excel files. What are There are various ways to begin Workbooks working with an Excel workbook. ? Excel files are called workbooks. You can either start from scratch or use a pre-designed template to create a new workbook. Creating and Opening Workbooks Steps to Create a new blank workbook: Step 1: Select the File tab Step 2: Click New Step 3: Click Blank Workbook Step 4: A new blank workbook will appear. Saving Workbook Whenever we create a new workbook in Excel and insert the data into it, then we must save our workbook so that our data is not lost. As in previous versions of Excel, you can save files locally to your computer You can also save your workbook to the cloud and also export your workbook with others. Save: When you save a file, you’ll only need to choose a file name and location the first time. Then you can just use the save command to save it with the same name and location. Save As: When you use Save As, you’ll need to choose a different name and /or location for the copied version. Columns and Rows Column: A column consisting of group of vertical cells and it is named by letters or a combination of letters. Rows: A row consisting of group of horizontal cells and each row present in a spreadsheet is named by a number. Cell Cell: In a everything like a numeric value, functions, expressions etc is recorded in the cell. Number of rows and columns Cell Referencing A cell reference also know as a cell address is a way for describing a cell on a worksheet that combines a column letter and a row number. We can refer to any cell on the worksheet using cell references (in excel formula). Changing column width When the text you type is longer than the width of the column, the overflow characters are displayed in the adjacent cell(s) to the right if they do not contain any data. Cont… You can also a column’s width by performing the following steps: Select a column or a range of columns. On the Home tab, in the Cells group, select Format > Column Width Enter the desired width and click OK. You can enter any value between 0 and 255. Changing row heights Changing the height of a row works the same as changing the width of a column. The row height is 15.00 by default. Select rows and then drag up or down to change the height of the selected rows Cont… Select a row or a range of rows. On the Home tab, in the Cells group, select Format>Row Width Type the row width and click OK. Automatically resize all columns This can be accomplished by selecting the column heads for those columns whose width you wish to change and then double-clicking between any of the column heads of the selected columns. You can also perform an Autofit by clicking the Home tab, in the Cells group, click the Format button and select AutoFit Column Width. You can apply the same procedure to change the row heights as well. Hiding columns in Excel The shortcut for hiding columns in Excel is Ctrl + 0. For the sake of clarity, the last key is zero, not the uppercase letter "O". To hide a single column, select any cell within it, then use the shortcut. To hide multiple columns, select one or more cells in each column, and then press the key combination. To hide non-adjacent columns, click on the header of the first column, press and hold the Ctrl key while clicking on each additional column to select them, and then use the hiding shortcut. Hide a column using the context menu Select one or several columns to be hidden. Right-click the selection and pick the Hide option from the menu. Hide a column using the ribbon Select the column or any cell within the column(s) you want to hide. On the Home tab, in the Cells group, click Format > Hide & Unhide > Hide Columns. (context menu and ribbon) You can apply same procedure to hide rows Unhide columns in Excel Click on a small triangle in the upper-left corner of your table to select the entire worksheet. Now just right-click the headings and pick the column Unhide option from the context menu. Show hidden columns Select the columns to the left and right of the column you want to unhide. For example, to show hidden column B, select columns A and C. Go to the Home tab > Cells group and click Format > Hide & Unhide > Unhide columns. Or Select the adjacent columns for the hidden columns. Right-click the selected columns, and then select Unhide. You can apply same procedure to unhide rows Data types in Excel In each cell , there may be the following types of data Number data Text data Logical data (TRUE or FALSE) Error data 1. Number Data Type Data is this category includes any kind of number. These may include large numbers or small fractions and quantitative or qualitative data. Monetary totals Whole numbers Percentages Decimals Dates Times Integers Phone numbers Number formats in Excel 2. Text Data Type Text data includes characters such as alphabetical, numerical and special symbols. The primary difference between number data and text data is that you can use calculations on number data but not text data. Excel may categorize figures it doesn't recognize as text data by default, so it's important to format your data to fit the type you want. Examples of text data may include: Words Sentences Dates Times Addresses Example: 3. Logical Data Types Logical values are often shown as either TRUE (or 1 in Boolean value) or FALSE (or 0 in Boolean value). Logical data will often be displayed as a result of a certain function or expressions. 4. Error Data Type There are instances in which errors will occur when Excel evaluates the contents of a cell. For example, division by zero is mathematically undefined, and the machine cannot, by itself, resolve this error. It turns out the Excel has an Error type specifically for this instance, the #DIV/0! result. Example: Arithmetic Operators The most basic operations are the standard multiply, divide, add and subtract. Comparison Operators Order of operations When using several operations in one formula, Excel follows the order of operations for math. First: all parentheses - innermost first Second: exponents (^) Third: all multiplication (*) and division (/).Do these starting with the leftmost * or / and work to the right. Fourth: all addition (+) and subtraction (-).Do these starting with the leftmost + or - and work to the right. Example: = 3+5 +(6*2) - 4 = 3+5+(12) - 4 = 8 +12 - 4 = 20 - 4 = 16 Example: Excel Formula You must have an equal sign ( = ) as the first character in a cell that contains a formula. The = sign tells excel that the contents of the cell is a formula Without the = sign, the formula will not calculate anything. It will simply display the text of the formula. Creating a simple formula Copying a formula Select the cell Copy the contents of the selected cell (Ctrl + C) Select the cell to which the formula is to be pasted Paste the contents on the selected cell (Ctrl + V) Copying a formula using the fill handle Select the cell which contains the formula Click the small black square in the bottom-right corner of the selected cell Drag the fill handle up to the required cell Any Questions?