Microsoft Office Introduction

Microsoft Office Introduction

1. A Little History…. 40 years ago, on April 4, 1975, Bill Gates and Paul Allen, friends from Lakeside School in Seattle, formed a little partnership. Gates was 19 and Allen 22. That partnership was called Micro-soft. The 40-year-long journey saw Microsoft evolving into one of the world's largest technology companies and is marked with numerous milestones…

2. “Is a leading global vendor who provides computer software, hardware, mobile and gaming systems and cloud services ” Its important to know the Microsoft Excel version history since the version 1 in 1985 and current version Office Standard 2019 . Collaboration Content m ana ge m en t St r e a ml i n e d processes Portals Business intelligence Search Word processing Business m odel i n g Presentations Business data management Information Manag e m en t 2019

3. Microsoft Office Productivity Tools for Home & Office

4. Microsoft Office Tools

5. Microsoft Office A package of softwares or a Suite of products developed by Microsoft Corporation that includes Word – text editor Excel – spreadsheet PowerPoint – presentations Outlook – e-mail Access – database Publisher – brochures, calendars, postcards, etc. The current version is Microsoft Office 2019 which was released on September 24, 2018. Succeeding Office 2016 & Office 365.

6. Useful tool used regulerly

7. Microsoft Word

8. Text processor to create documents Templates for different types of documents Web pages Microsoft Word or MS-WORD (often called Word) is a graphical word processing program that users can type with. It is made by the computer company Microsoft. The purpose of the MS Word is to allow the users to type and save documents. Similar to other word processors, it has helpful tools to make documents . Simple formatting Text: Font, size, colour, bold/italic/underline Layout: Bullets, numbering, alignment Microsoft word

9. Uses of Microsoft word With this application, we can check for grammar and spelling errors. We can also get visual edge through text modifications. With the WordArt feature, we can choose a compelling design that can match the theme. Besides the WordArt feature, we can also modify the visual look of our text the way we want to. Sending Letters News Articles Scholarly Documents Envelopes Blog Posts Homework and Assignments Publishing of Books Compelling Documents

10. Microsoft Word

11. Microsoft PowerPoint

12. Software to make presentations Templates and different designs Same menus as Microsoft Word PowerPoint is a complete presentation graphics package. It gives you everything you need to produce a professional-looking presentation. PowerPoint offers word processing, outlining, drawing, graphing, and presentation management tools--all designed to be easy to use and learn . Microsoft PowerPoint

13. Microsoft PowerPoint

14. Text Font should be easy to read Text colour with high contrast to the background Big font size Less text is better Use the same formatting throughout the presentation Pictures and figures Choose understandable figures Pictures with high quality Pictures and figures should be relevant How to make a nice presentation

15. Be careful with use of animations Know what you are presenting Don’t read from the slide Speak to the people Speak loudly Practice before presentation! Presentation Tips

16. Microsoft outlook

17. Microsoft Outlook is a personal information manager from Microsoft, available as a part of the Microsoft Office suite. Although often used mainly as an email application, it also includes a calendar, task manager, contact manager, note taking, journal, and web browsing. Microsoft outlook

18. Microsoft outlook

19. Uses of Microsoft outlook This tool is used to write standard & professional emails To sort and keep track of emails. Organize and retrieve information quickly. Manage your schedule by making appointments. Let Outlook remind you of class meetings, dues dates, bills, or anything else you ask it to. Invite others to appointments you make. View the calendars of other Outlook users .

20. What can I do with E-mails in Outlook? Make respective folders to categorize. Personalize E-mails with custom signatures. Personalize with themes. Insert files, folders, pictures, etc. Attachments Importance/Priority Ratings Spell Check Scheduled Delivery

21. Email Communication & Etiquette Manners & Principal communication on the Internet Rules for how to communicate appropriately and respectfully online 1. What is email etiquette? 2. Why does it matter? Good email communication skills. . . • are part of succeeding in college and on the job. • help you make a good impression. • make it more likely that you’ll get the response you want!

22. Basic Rules of Email Communication Be sure to include the following : - Proper subject line - Greeting - Introduction (if necessary) - Request - Thank you / Closing remark - Signature

23. Basic Rules of Email Communication Remember that direct language can sound harsher in emails than in person. Use proper written, not spoken, language. Do not assume that the reader will “get” a joke or understand sarcasm. Be thoughtful and respectful in what you write. Do not make demands. Choose your words carefully . Assume everything you write is a public document. Avoid talking about other people in an email message. Use proper grammar and sentence structure. Spellcheck your message!

24. Basic Rules of Email Communication If your message includes a request, always close with a thank you to the recipient for considering it. Do not expect or demand an immediate response. Reread your message before sending it, and rephrase if necessary . If you receive a confusing or unclear email message . . . - give the writer the benefit of the doubt. - ask politely for clarification. - suggest discussing the matter in person.

25. Microsoft Excel

26. Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows, Mac OS X, and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications . Also known as & for, Mathematical software Spreadsheets to make statistical calculations Formula for calculations of different results in a set of data In & Out data Graphical view of statistics Microsoft Excel

27. Uses of Microsoft Excel Build great charts Using pie charts, graphs and clustered columns adds meaning to data, which otherwise may just exist as row after row of numbers. These visualisations can add extra emphasis to business reports and persuasive marketing material . Use conditional formatting Excel users can format their spreadsheets using different colours, bolds and italics, to differentiate between columns and bring the most important data to the fore

28. Uses of Microsoft Excel Help identify trends Excel allows trend lines to be extended beyond the graph, to offer predictions of future activity - and such forecasts can help businesses develop their future strategy. Bring data together Excel can be used to bring information from various files and documents together, so that it exists in a single location. As well as raw data and information from other spreadsheets, it is possible to import text and images. Other objects can be added using the Insert tab, or additional spreadsheets can be added to the file .

29. Microsoft Excel

30. Excel Basics

31. Table of Contents Getting Started The New Interface Excel Basics

32. Microsoft Excel - Evaluation

33. What is Excel? Getting Started Excel is a computerized spreadsheet, which is an important business tool that helps you in preparing dynamic reports in all Domains. Excel is software that lets you create tables, calculate and analyse data. This type of software is called spreadsheet software: Powered by Microsoft What is Spreadsheet? A type of application program which manipulates numerical and string data in rows and columns of cells. The value in a cell can be calculated from a formula by involving other cells. Any value entered, excel calculates it automatically, from different cells with different formats.

34. Common Areas/Domain where Excel is Used 7 Popular Business Uses for Microsoft Excel Accounting - Cash-flow, income P&L statement etc. Budgeting - Marketing, event, retirement etc. Billing and sales - Sale invoice, purchase order etc. Reporting - Project performance, forecast data. Etc. Planning - Weekly report, yearly report etc. Tracking - Timesheet, to do list etc. Data entry and storage Collection and Verification of Business Data’ Administrative and managerial duties Accounting and budgeting Data Analysis Reporting + Visualizations Forecasting

35. The Beginner’s Guide to Microsoft Excel

36. Worksheet and workbook specifications & limits Each workbook is made up of individual worksheets or sheets or Spreadsheets Excel stores spreadsheets in a document called workbook Sheets in a workbook - Limited by available memory (default is 3 sheets ) We come across Rows, Columns & Cells in Sheets Total number of rows and columns on a worksheet 1,048,576 Rows by 16,384 Columns Number Precision - 15 digits

37. Navigating cells on worksheet These are keys you will require almost always. And, that’s because Excel is all about data in cells. When you are working on one, you know how often you have to move from cell to cell and from one end to the other To Perform Action Press Keys Move one cell up, down, left, or right Arrow Keys Move one cell to the right Tab Move one cell to the left Shift + Tab Move to the edge of the current data region Ctrl + Arrow Key Move to the beginning of the row Home Move to the beginning of the worksheet Ctrl + Home Move to the next empty cell of the row End Move to the last unused cell in the bottom-most row Ctrl + End To move down one screen Page Down To move up one screen Page Up To move one screen to the right Alt + Page Down To move one screen to the left Alt + Page Up To move between unlocked cells on a protected worksheet Tab

38. Ribbon and Tabs in Excel Ribbon is on the top of worksheet, below the title bar or name of the excel file. It contains seven tabs : Home Insert Page Layout Formulas Data Review View Each tab has its own specific groups of related commands. These groups have several additional commands that can be viewed by clicking the arrow at the right bottom corner of any group.

39. Home The Home Tab in Microsoft Excel has a lot of functionality for number crunching built right into it. You can do things like formatting, alignment, inserting and deleting rows or columns, sorting and filtering numbers, applying styles and formatting effects, finding and replacing data and much more using the Tab.

40. 2. Insert The insert Tab in Microsoft Excel 2010 has a lot of functionality for analysing. You can do things like Table, Charting, Links, Symbols, Text decoration and much more using the Tab.

41. The page layout Tab in Microsoft Excel 2010 has a lot of functionality for printing. You can do things like page setup, scaling, sheet option and much more using the Tab. 3. Page Layout To use predefined margins, click Normal, Wide, or Narrow, custom margin setting, specify custom page margins, click Custom Margins...

42. 4. Formulas The Formulas Tab in Microsoft Excel 2010 greatly simplifies the task of number crunching. The Excel Formulas Tab has the following groups : Function Library Defined Names Formula Auditing Calculation

43. Using this tab, you can import data from external sources including but not limited to a text files, Microsoft Access databases, web pages, xml documents, Microsoft Query, Microsoft SQL Server databases. 5. Data Extracting data from external sites Sorting Text to Columns Remove Duplicates Data Validations (Drop downs)

44. 6. Review This Tab has functionality that will let you proof read your Excel workbooks, add and delete comments, protect and unprotect Excel sheets / workbooks & finally allow users to track changes in a multi user Excel workbook. Spell Check Translate Language Add Comments Protect Sheets Ink

45. 7 . View Every tab has its own importance in Excel ribbon in which View tab helps to change the view of Excel sheet and make it easy to view the data. Also, this tab is useful for preparing the workbook for printing.

46. III. Excel Basic Basic Short Cut Key’s

47. Excel Formula Errors – Understand and Debug Them The following are some errors that may appear in a spreadsheet (there are others too) - ####### Cell is too narrow to display the results of the formula. To fix this simply make the column wider and the “real” value will be displayed instead of the ###### signs. Note that even when the ###### signs are being displayed, Excel still uses the “real” value to calculate formulas that reference this cell. - #NAME? You used a cell reference in the formula that is not formed correctly (e.g. =BB+10 instead of =B3+10) - #VALUE! Usually the result of trying to do math with a textual value. Example: =A1*3 where A1 contains the word “hello”

48. - #DIV/0! Trying to divide by zero. Example: =3/A1 where A1 contains 0 (zero) - Circular Reference Using a formula that contains a reference to the cell that the formula “lives in”. Example: putting the formula =A1+1 in cell A1 or putting the formula =SUM(A1:B2) in any of the cells A1, B1, A2, B2 #N/A this is one of the frequent errors you see while using VLOOKUP formula. The N/A error is shown when some data is missing, or inappropriate arguments are passed to the lookup functions (VLOOKUP, HLOOKUP etc.) #REF! This is one of the most common error messages you see when you fiddle with a worksheet full of formulas. You get #REF! error when one of the formula parameters is pointing to an invalid range. This can happen because you deleted the cells. For e.g. try to write a sum formula like =SUM(A1:A10, B1:B10, C1:C10) and then delete the column C. Immediately the sum formula returns #REF! error.

49. - Reference of cells 1. Relative References: Automatically change as they’re copied down a column or across a row. 2. Absolute References: They are fixed. They don’t change if you copy a formula from one cell to another. Absolute references have dollar signs ($) like this: $D$9 – Using F4 between each controls

50. Function Useful Short Keys Filter: Alt + D + F + F Filter: Ctrl + Shift +L Filter: Alt + A + T Auto Sum: Alt+= Current Date: =TODAY() Shortcut Key: Ctrl+; Current Date And Time: =NOW() Current Time: =NOW()-TODAY() (Use Ctrl+Shift+2) to convert time decimals Key For Date And Time: =Ctrl+;< Space>Ctrl + Shift +; =IF – Is for Single condition =IFS – If it is Multiple Conditions

51. Advance Excel Training

52. t opics Filtering Data Sorting Table Formats Data Validation Conditional Formatting V-Lookup & H-Lookup Pivot Tables Charts/Graphs Formulas

53. 1. Filtering The basic Excel filter (also known as the Excel  Auto-filter) allows you to view specific rows in an Excel spreadsheet, while hiding the other rows. When the Excel  auto-filter is added to the header row of a spreadsheet, a drop-down menu appears in each cell of the header row. Why Filtering? We can focus on specific record/rows in a large table of data by setting conditions, so that only specific data is displayed and rest of the records are get hidden temporarily in the spreadsheet .

54. Filters Filtering data in MS Excel refers to displaying only the rows that meet certain conditions. (The other rows get hidden temporarily ). Path for Filter option: Data <> Sort & Filter Shortcut keys: Ctrl + Shift + L Alt + D + F + F Alt + A + T

55. 1. Advanced Filtering Actions for Advance Filtering : Filter the list, In the same place by Criteria Filter the list, Copy to another location Criteria

56. 2. Text Filtering Equals Does not Equal Begins with Ends with Contains Does not begin with Custom Filter

57. 3. Label & Value Filtering Important: This selection is reflected only on Pivot Tables

58. 4. Multiple Filters As we all are aware that a Filter can be placed only once in a sheet and multiples in multiple sheets or work book With the help from Data <> Sort & Filter Or Shortcut keys: Ctrl + Shift + L Alt + D + F + F Alt + A + T

59. 4.1 Multiple Filters And we can place Multiple Filters in one sheet, but using Tables Format, under Styles,

60. Data Sorting Data Sorting in MS Excel is performed to re-arranges the rows based on the contents of a particular column. For numerical data, Sorting could be done in ascending order or descending order. For text data, sorting could be done in alphabetical ascending (A-Z) order or descending order (Z-A). Tips & Tricks from drop-down menu Alt + i + S   (Sort A to Z) Alt + i + O   (Sort Z to A) Alt + i + T   (Sort by Color sub menu) Alt + i + I   (Filter by Color sub menu) Alt + i + F (Text or Date Filter sub menu)

61. Data Validation MS Excel data validation feature allows you to set up certain validation rules that what can be entered into a cell. Validation Criteria To specify the type of data allowable in a cell or range, follow the steps below: Select the cell or range. Choose Data » Data Tools » Data Validation. Excel displays its Data Validation dialog box having 3 tabs- Settings Input Message Error alert

62. 1. Settings Here you can set the type of validation you need. Choose an option from the Allow drop-down list. The contents of the Data Validation dialog box will change, displaying controls based on your choice – Any Value Whole Number Decimal List etc.

63. 2. Input Message With the options available in data validation, you can display messages to give instructions to the people who use your spreadsheet. An Input Message can be displayed when a cell is selected. An Error Alert can be displayed if invalid data is entered in a cell.

64. 3. Error Alert! Create an Error Alert! When you add data validation to a cell, the Error Alert feature is automatically turned on. It blocks the users from entering invalid data in the cell. You can turn Error Alert off, to allow people to enter invalid data

65. Conditional Formatting Conditional formatting helps to visually explore and Analyze data, Detect critical issues, and Identify patterns and trends. Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, Color scales, and icon sets that correspond to specific variations in the data. Choose Home Tab » Style group » Conditional Formatting dropdown .

66. Conditional Formatting New Rule: It opens the New Formatting Rule dialog box, where you define a custom conditional formatting rule to apply to the cell selection . Clear Rule: It opens a continuation menu, where you can remove conditional formatting rules for the cell selection by clicking the Selected Cells option, for the entire worksheet by clicking the ‘Entire Sheet’ option, or for just the current data table by clicking the ‘This Table’ option . Manage Rule: It opens the Conditional Formatting Rules Manager dialog box, where you edit and delete particular rules as well as adjust their rule precedence by moving them up or down in the Rules list box.

67. V-Lookup & H-Lookup V-LOOKUP is a function, which is used to search for value vertically from lookup table. Similarly, H-LOOKUP searches for value horizontally from lookup table . V-LOOKUP and H-LOOKUP both have 4 parameters as below . (lookup_value , table_array, col_index_num , range_lookup )

68. V-Lookup & H-Lookup Parameters L ookup_value : It is the user input. This is the value that the function uses to search on. The table_array : It is the area of cells in which the table is located. This includes not only the column being searched on, but the data columns for which you are going to get the values that you need. Col_index_num : It is the column of data that contains the answer that you want . Range_lookup : It is a TRUE or FALSE value. When set to TRUE, the lookup function gives the closest match to the lookup_value without going over the lookup_value. When set to FALSE, an exact match must be found to the lookup_value or the function will return #N/A. Note, this requires that the column containing the lookup_value be formatted in ascending order.

69. Pivot Table In the row and column label areas of a PivotTable report, you can group the items in a field by customize ways. Grouping data can help you to create a subset of data, which cannot be easily grouped in other ways, such as sorting and filtering. What can we do with Pivot Table? We can do dimension wise grouping What to show in a group? Any possible dynamic summary of the group Shortcut keys Alt + D + P + F Alt + N + V + Enter

70. Pivot Charts A pivot chart is a graphical representation of a data summary displayed in a pivot table. A pivot chart is always based on a pivot table. Although Excel lets you create a pivot table and a pivot chart at the same time, you can’t create a pivot chart without a pivot table. All Excel charting features are available in a pivot chart. Pivot charts are available under Insert tab » PivotTable dropdown » PivotChart

71. Charts/Graphs Graph or Chart translate the data into distances and plot data points in a way that their relative distances are kept. Select the chart of your choice and click OK to generate the chart. To create charts for the data by below steps. Select the data for which you want to create chart. Choose insert tab » select the chart or click on the chart group to see various chart types.

72. Types of Graphs/Charts Bar : A bar chart illustrates comparisons among individual items Area : An area chart emphasizes the magnitude of change over time. Line : A line chart shows trends in data at equal intervals. Column : Column chart shows data changes over a period of time or illustrates comparisons among items. Pie : A pie chart shows the size of items that make up a data series, proportional to the sum of the items.

73. Formulas 15 Excel Formulas, Keyboard Shortcuts & Tricks That'll Save You Lots of Time

74. Thank You