Excel Basics

What is Excel ?

• Spreadsheet developed by Microsoft for Windows ,MacOS, Android and IOS
• It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.
• Excel forms part of the Microsoft Office suite of software.
• https://www.microsoft.com/en-us/microsoft-365/excel

What you see

• Work Book (WorkSheet)
• Sheet (Spreadsheet)
• Cells (using a grid of cells arranged in numbered rows and letter-named columns )

• Workbook
• workbook is a collection of one or more spreadsheets, also called worksheets, in a single file. 
• Above image has workbook with 3 spreadsheets.

• Difference between workbook and worksheet
• The worksheet is single page spreadsheet or page in Excel, where you can write, edit and manipulate data, whereas the collection of such worksheets is referred as a workbook. It is very similar to a single page (worksheet) and a complete book (workbook).
• What is ribbon?
• Ribbon refers to the topmost area of the application that contains menu items and toolbars available in MS-Excel. Ribbon can be shown/hidden using CTRL+F1. The ribbon runs on the top of the application and is the replacement for the toolbars and menus. The ribbons have various tabs on the top, and each tab has its own group of commands.

Excel Basic Operations

• Identify Workbook, worksheet, cell, …
• Work with Excel
• Save in different file formats
• Use built in functions
• Help function
• Enter data to cell. create list , Select , sort, copy and move data
• Edit rows , columns in worksheet. Copy , move, delete, rename work sheet
• Create mathematical and logical formulas , recognize error
• Format number and text in excel sheet
• Choose charts to communicate information meaningfully
• Adjust spread sheet page settings and check and correct content before print

Data Formats and Formatting

• How many are available ? Why we need ?
• 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

• How to get
• Select a cell. Right click and select “Format Cells”. 
• You will get “Format Cells”. 
• You can select the category as for your need

1. Right Click Menu

2. After selecting "Format Cells"

Order of the operations

• The order of operations in Microsoft Excel is same as in standard mathematics. 
• It's defined by the term "PEMDAS" or "BEDMAS".

• Parentheses or Brackets
• Exponent
• Multiplication
• Division
• Addition
• Subtraction
• How can we wrap text
• 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.
• Prevent copy
• To protect your worksheet from getting copied
• Menu bar >Review > Protect Sheet > Password. By entering a password, you can secure your sheet from getting copied by others.                                                     
• Name box
• Name Box displays the cell that is currently selected in the spreadsheet. 
• It is located to the left of the formula bar. 
• If a name is defined for a cell that is selected, the Name Box displays the name of the cell. 
• You can use the Name Box to define a name for a selected cell as well.

Macro

• Macros are used for iterating over a group of tasks.
• Users can create macros for their customized repetitive functions and instructions. 
• Macros can be either written or recorded depending on the user.
• Macro languages in MS-Excel
• XLM and VBA (Visual Basic Applications)
• Earlier versions of Excel used XLM.
• VBA was introduced in Excel 5 and mostly used now.

Charts

• Graphical representation of the data in Excel
• Select an option from Insert tab's Chart group.
• A user can use any chart type as per requirement
•  including column
• bar 
• Line
•  pie
•  scatter, etc.

Explain some useful functions

• How you sum up rows and columns ? 
• SUM function
• Do you really need to call function. Select the cells, rows and see the bottom right corner
• Math and Financial Functions – SQRT, DEGREE, RAND(), GCD-greater common divisor
• Logical Functions – IF, AND, FALSE, TRUE
• Date and Time functions – NOW(), DATEVALUE(), WEEKDAY(NOW())
• Index Match – VLOOKUP and INDEX MATCH
• Pivot tables

Pivot Table

• Tool that allows for quick summarization of large data.
•  It automatically performs a sort, count, total or average of the data stored in the spreadsheet and displays result in another spreadsheet. 
• It saves a lot of time. Allows to link external data sources to our Excel.
• You can take sample data sheet from internet
• https://www.contextures.com/xlSampleData01.html
• Go to Insert --> Pivot Chart and Pivot Tables
• Depend on requirement you can select options. Try and see

Report

• Load a workbook into Excel
• Select the top-left cell in the source data
• Click on Data tab in the navigation ribbon
• Click on Forecast Sheet under the Forecast section to display the Create Forecast Worksheet dialog box
• Choose between a line graph or bar graph
• Choose Forecast end date
• Click Options for customization
• Select Forecast start date

References

https://www.windowscentral.com/creating-reports-excel-2016