LECTURE 8 - Scope I. Practice MCQs II. Microsoft OFFICE I. INTRODUCTION II. MS WORD III. MS EXCEL IV. MS POWERPOINT MICROSOFT EXCEL Important Points: • Majorly used for making spreadsheets • A spreadsheet consists of grids in the form of rows and columns which is easy to manage and can be used as a replacement for paper • It is a data processing application • Calculations can be done based on the large amount of data entered into the cells of a spreadsheet within seconds • File extension, when saved in the computer, is “.xls” • Other apps similar to Excel – VPP, Lotus 1,2,3 MICROSOFT EXCEL Important Points: • How to name a CELL ? • How to name a RANGE? • Default Active Cell – A1 • Worksheets – Shortcut for a new worksheet • What is “^” sign used for? • What is the priority of operations ? • % ^ * / + - = > < <= >= <> • Editing of a cell – F2 • Default name of file – BOOK1 MICROSOFT EXCEL Important Points: • Can you use HYPERLINK in EXCEL? • No. of Rows & Columns in 2010, 2013 & 2016 – 1,048,576 & 16,384 • No. of Rows & Columns in 2003 – 65,536 & 256 • Extensions of EXCEL – • 2013 - .XLS • After 2013 - .XLSX • If an excel sheet has MACROS - .XLSM • If you want to save a template - .XLTX • Formula can start from – Equal to (=) • What is MERGING? MICROSOFT EXCEL MATH FUNCTIONS STATISTICAL FORMULAs 1. ABS() → =ABS(-50) 1. MAX() → =MAX(5,19,3,44,23,5,90) 2. INT() → =INT(4.9) 2. MIN() → =MIN(5,19,3,44,23,5,90) 3. SUM() → =SUM(5,10,3) 3. AVERAGE() → =AVERAGE(5,19,3,44,23,5,90) 4. SQRT() → =SQRT(81) 4. COUNT() → =COUNT(5,19,3,44,23,5,90) 5. POWER() → =POWER(5,2) 6. ROUND() → =ROUND (5.67) MICROSOFT EXCEL TEXT FORMULAS 1. UPPER → =UPPER(“excel”) 2. LOWER → =LOWER(“Excel”) 3. CONCATENATE → =CONCATENATE(“PA”,”SS”) 4. EXACT → =EXACT(“excel","Excel”) 5. LEFT → =LEFT(“SOFTWARE”,4) 6. RIGHT → =RIGHT(“SOFTWARE,4) 7. LEN → =LEN(“SOFTWARE”) 8. TRIM → =TRIM(“ SOFTWARE “) MICROSOFT EXCEL DATE & TIME FORMULAS LOGICAL FORMULAs 1. DATE → =DATE(“2021,2,24”) 1. AND → =AND(15>20,2=2) 2. TODAY → =TODAY() 2. OR → =OR(15>20,2=2) 3. NOW → =NOW() 3. NOT() → =NOT(15>20) 4. YEAR → =YEAR() 4. IF() → =IF(35>20,”Yes”,”No”) 5. MONTH → =MONTH() 6. DAY → =DAY() Keys Description Keys Description F2 Edit active cell Shift + F11 Insert new worksheet F5 Go to Dialog box Shift + F9 Calculate the active worksheet
F9 Calculate worksheets Ctrl + Shift + ; Time
F4 Create an absolute Ctrl +; Date
Tab Right one Cell F11 Create Chart Shift + Tab Left One Cell Ctrl + Shift + U Expand/Collapse formula bar Ctrl + Home To first Cell Alt + = Insert AutoSum formula Ctrl + End To Last Cell Ctrl + ~ Show/hide all formula IMPORTANT SHORTCUTS Shift + spacebar Select entire row Ctrl + Shift + @ Apply the time format
Ctrl + Spacebar Select entire column Ctrl + Shift + # Apply the date format
Ctrl + 9 Hide selected rows Ctrl + Shift + % Apply the percentage format without decimal
Ctrl + 0 Hide selected columns Ctrl + Shift + $ Apply the currency format with two decimals Arrow Keys Move between Cells Alt + Enter Start a new line in the same cell
Alt + Spacebar Display control Menu Ctrl + Shift + Home Extend the selection of the cells
F1 Create Pivot Chart Alt + F1 Create Pivot Chart in the same worksheet
Microsoft Excel For Beginners: The Complete Guide To Mastering Microsoft Excel, Understanding Excel Formulas And Functions Effectively, Creating Tables, And Charts Accurately, Etc (Computer/Tech)
Microsoft Excel For Beginners: The Complete Guide To Mastering Microsoft Excel, Understanding Excel Formulas And Functions Effectively, Creating Tables, And Charts Accurately, Etc (Computer/Tech)
