Excel Tutorial PDF

Excel What is Excel Microsoft Excel is a spreadsheet program developed by Microsoft. It is widely used for various tasks involving data organization, analysis, and visualization. Excel provides a grid of cells arranged in rows and columns, where users can enter and manipulate da...

Excel What is Excel Microsoft Excel is a spreadsheet program developed by Microsoft. It is widely used for various tasks involving data organization, analysis, and visualization. Excel provides a grid of cells arranged in rows and columns, where users can enter and manipulate data. The cells can contain numbers, text, formulas, and functions. Key features of using excel Worksheets and Workbooks: Excel documents are called workbooks, and each workbook is divided into worksheets. Users can have multiple sheets within a workbook to organize and analyze different sets of data. Cells, Rows, and Columns: Data is entered into individual cells, and cells can be organized into rows and columns. Columns are labeled with letters (A, B, C, etc.), and rows are labeled with numbers (1, 2, 3, etc.), and the cell merges column letters and rows number (A5, D2). Key features of using excel Formulas and Functions: Excel allows users to perform calculations on data using formulas and functions. Formulas are user-created equations, while functions are predefined formulas for common mathematical, statistical, and financial operations. Charts and Graphs: Excel has robust charting capabilities, allowing users to create a variety of charts and graphs to visually represent data trends and patterns. Data Analysis Tools: Excel provides various tools for data analysis, including sorting, filtering, and pivot tables. These tools help users organize and analyze large sets of data efficiently. Key features of using excel Conditional Formatting: Users can apply formatting rules to cells based on certain conditions. This helps highlight specific data points or trends. Data Import and Export: Excel supports the import and export of data from/to various formats, making it easy to work with data from different sources. Collaboration: Excel files can be shared and edited collaboratively in real-time, allowing multiple users to work on the same document simultaneously. Create new workbook in Excel  Sign in to office.com → Select Excel to open the online app. 6 Main Excel Interface Workbook column A Pointer on cell A1 Formula bar row 1 cell A1 Worksheets Add new sheet To change permission Allow editing of workbook Prevent editing of workbook Rename Workbook Rename Worksheet Click on file name Click on sheet name Insert, delete, hide column Select the column you want to insert or hide by click on column letter. Right-click on the selected column. Choose the option (insert, delete, hide) from the context menu. Insert, delete, hide Rows Select the rows you want to insert or hide by click on row number. Right-click on the selected rows. Choose the option (insert, delete, hide) from the context menu.  Unhide rows, columns To unhide specific rows or columns, select the rows/columns above/below and below the hidden rows. Right-click on the selection. Choose "Unhide" from the context menu. Col B is hidden Row 4 is hidden Freeze Panes Freezing panes in Excel allows you to keep certain rows or columns visible while scrolling through the rest of the worksheet. This is useful when working with large datasets. Unfreeze Panes If you want to unfreeze panes, go to the "View" tab, click on "Freeze Panes" in then choose "Unfreeze Panes."  Move cell contents Move Cells: 1.Select the Cells: 1. Click and drag to select the cells you want to move. 2.Cut the Cells: 1. Right-click on the selected cells. 2. Choose "Cut" from the context menu. 3.Navigate to the Destination: 1. Go to the cell where you want to move the selected cells. 4.Paste the Cells: 1. Right-click on the destination cell. 2. Choose "Insert Cut Cells" from the context menu. OR You can use the keyboard shortcuts: Cut: Ctrl + X Navigate to the destination cell and paste: Ctrl + V  Copy cells and cell contents Copy Cells: 1.Select the Cells: 1. Click and drag to select the cells you want to copy. 2.Copy the Cells: 1. Right-click on the selected cells. 2. Choose "Copy" from the context menu. 3.Navigate to the Destination: 1. Go to the cell where you want to copy the selected cells. 4.Paste the Cells: 1. Right-click on the destination cell. 2. Choose "Paste" from the context menu. OR You can use the keyboard shortcuts: Cut: Ctrl + c Navigate to the destination cell and paste: Ctrl + v  AutoFill AutoFill is a feature in Microsoft Excel that allows you to quickly fill a series of cells with a pattern or sequence. It is particularly useful for saving time when entering repetitive or sequential data. AutoFill can be used to fill cells with numbers, dates, text, or custom lists. Basic AutoFill: 1.Fill a Series of Numbers: 1. Type a number in a cell. 2. Hover over the small square (the fill handle) at the bottom-right corner of the cell until the cursor changes to a small black cross. 2.Drag to Fill: 1. Click and drag the fill handle down or across to fill adjacent cells with a series of numbers.  Number Formats Quick Number Format Options: 1.Number Format Dropdown: In the "Number" group on the ribbon or by click on right-click of the cells and select number format , you'll find a dropdown with quick number format options. You can choose from options like General, Number, Currency, Percentage, etc. 2.Increase/Decrease Decimal: You can use the "Increase Decimal" and "Decrease Decimal" buttons in the ribbon to quickly adjust the number of decimal places for the selected cells. Number Formats  Conditional formatting Conditional formatting in Microsoft Excel allows you to format cells based on specific conditions or rules. This feature is useful for visually highlighting data trends, outliers, or other important information within your spreadsheet. Basic Steps for Applying Conditional Formatting: 1. Select the Cells 2. Go to the "Home" Tab: 3. Conditional Formatting Menu in style group: 4. Choose a Rule: 5. Set Formatting Options: Conditional formatting  From Home tab Sorting Data Sorting Data: 1.Select the Range: 1. Click on a cell within the column you want to sort. 2. Drag to select the range of cells you want to sort. 2.Go to the "Data" Tab then select “Custom sort”: From Data tab Or you select Custom sort form ” Sort or filter” icon from Home tab 3.Sort Ascending or Descending: 1. In the "Sort & Filter" group, you'll find the "Sort A to Z" (Ascending) and "Sort Z to A" (Descending) buttons. 2. Click on the appropriate button to sort the selected range. 4.Custom Sorting: For more complex sorting options, you can use the "Sort" dialog from the "Data" tab. This allows you to sort by multiple columns, specify custom sort orders, and more. Sorting Data Filtering Data: 1.Select the Range: 1. Click on a cell within the column you want to filter. 2. Drag to select the range of cells you want to filter. 2. Go to the "Data" Tab the "Sort & Filter" : From Home tab Or you select Custom sort form ” Sort or filter” icon from Home tab 3. Filter: 1. In the "Sort & Filter" group, you'll find the "Filter" button. 2. Click on the "Filter" button to apply filters to the selected range. 4. Filter Options: 1. Once filters are applied, you'll see dropdown arrows in the header cells of the filtered column(s). Click on From Data tab these arrows to access filter options. 2. You can filter by specific values, sort the filtered data, and more. 5. Clear Filters: To clear filters, go back to the "Data" tab and click on the "Filter" button again. Filtering Data: Creating a Table: 1.Select Your Data: 1. Click and drag to select the range of cells that you want to include in your table. 2. Go to the "Insert" Tab: 3. Click on "Table": 4. Confirm Table Range: Ensure that the "Create Table" dialog box displays the correct range for your data. 5. Check "Create Table with Headers": If your data has headers, make sure the checkbox for "Create Table with Headers" is selected. 6.Click "OK": Common Paste Special Options: 1. Paste Values: Paste only the values without any formatting or formulas. 2. Paste Formulas: Paste only the formulas without any formatting or values. 3. Paste Formatting only: Copy only the formatting of the selected cells. 4. Transpose: Swap rows with columns and vice versa.  Protection In Microsoft Excel, you can protect your workbook, worksheets, and individual cells to control access and prevent unwanted changes. Workbook Protection: 1.Protecting the Entire Workbook: 1. Go to the "Review" tab on the Excel ribbon. 2. Click on "Protect Workbook" 3. Set a password if you want to restrict access to the workbook. 2.Unprotecting the Workbook: To unprotect the entire workbook, go to the "Review" tab and click on "Unprotect Workbook." Enter the password if it's protected. Charts A chart is a visual representation of data used to present information in a graphical format. Charts are commonly used in various fields, including business, finance, science, and education, to help convey complex data in a more understandable and accessible way. Charts provide a way to interpret data trends, patterns, and relationships. Create a chart in Excel 365: 1.Enter Data: Open Excel and enter your data into the cells. Ensure that the data is organized in a way that makes sense for the type of chart you want to create. 2.Select Data: Highlight the data you want to include in your chart. 1.Insert Chart: 1. Go to the "Insert" tab in the Excel ribbon. 2. Click on the "Chart" option. You'll see a variety of chart types, such as Column, Line, Pie, etc. 3. Choose the specific chart type you want to create. You can hover over each chart type to see a preview, and you can also select "All Charts" to see more options. 2.Customize Chart: 1. After inserting the chart, you can customize it further. Right-click on different elements of the chart (like axis labels, data points, etc.) to access options for formatting and customization. 2. You can also use the Chart Design and Format tabs in the ribbon to access additional customization options. 3.Chart Title and Labels: 1. Add a title to your chart by clicking on the placeholder text and typing your desired title. 2. Edit axis labels by clicking on them and typing your own labels. Here are some common types of charts: 1. Column Chart: Displays data in vertical columns. Useful for comparing values across categories. 2. Bar Chart: Similar to a column chart but with horizontal bars. 3. Line Chart: Represents data points with lines, often used to show trends over a continuous axis, such as time. 4. Pie Chart: Divides a circle into sectors to represent data proportions of a whole. 5. Scatter Plot: Displays individual data points on a two-dimensional graph, useful for showing relationships between two sets of data. Formulas and Functions Formulas and functions are powerful tools for performing calculations and manipulating data. Formulas are user-created equations, while functions are predefined formulas for common mathematical, statistical, and financial operations. The "Formulas" tab in Microsoft Excel provides a variety of tools and functions for working with formulas, performing calculations, and managing data. Formulas: 1. Basic Arithmetic Operators: + (Addition) - (Subtraction) * (Multiplication) / (Division) 2. Cell References: Use cell references in formulas to refer to specific cells. For example, A1 refers to the content in cell A1. 3. Example of a Simple Formula: =A1 + B1: Adds the contents of cells A1 and B1. 4. Functions: Formulas can include functions, which are predefined operations that perform specific calculations. Insert functions using direct typing 1. Start with Equals Sign: In the cell where you want to insert the function, start by typing an equal sign (=). This signals to Excel that you're entering a formula or function. 2. Type the Function Name: After the equals sign, type the name of the function you want to use. Excel will provide suggestions as you type, and you can use the arrow keys to select the function you want. For example, if you want to use the SUM function, type =SUM(. 3. Enter Function Arguments: Open parentheses (() to start entering the function's arguments. Enter the range or values the function should operate on. For example, if you want to sum the values in cells A1 to A10, you would type =SUM(A1:A10). 4. Close Parentheses and Press Enter: After entering the arguments, close the parentheses ()) and press Enter. Excel will calculate the result of the function and display it in the cell. Insert functions using the Insert Function Dialog box Statistical Functions: Insert functions 1. SUM Function: using Auto Sum Adds up all the numbers in a range. Example: =SUM(A1:A10) 2. AVERAGE Function: Calculates the average of a range of numbers. Example: =AVERAGE(B1:B10) 3. COUNT Function: Counts the number of cells that contain numbers in a range. Example: =COUNT(C1:C10) 4. MIN and MAX Functions: Example: =MIN(A1:A10): Returns the smallest value in the range A1 to A10. Example: =MAX(B1:B10): Returns the largest value in the range B1 to B10. Date and Time Functions: =TODAY(): Returns the current date. =NOW(): Returns the current date and time. =YEAR(“1/1/2024”): Returns the year of a date ➔ 2024. =MONTH(“1/12/2023”): Returns the number month of a date ➔ 12. =DAY (“31/12/2024”): Returns the day of the month ➔ 31. Count Function Family COUNT: Counts the number of cells in a range that contains numbers. Example: =COUNT(A1:A5) COUNTA: Counts the number of non-empty cells in a range. Example: =COUNTA(A1:A5) COUNTBLANK: Counts the number of blank cells in a range. Example: =COUNTBLANK(A1:A5) COUNTIF: ➔ Counts the number of cells in a range that meet a specific condition. Example: =COUNTIF(A1:A5, ">50") Logical functions 1. IF Function: Syntax: =IF(logical_condition, value_if_true, value_if_false) Example: =IF(A1>50, "High", "Low") 2. AND Function: Syntax: =AND(condition1, condition2,...) ➔ the answer (True, False) Example: =AND(A1>50, B1="Yes") 3. OR Function: Syntax: =OR(condition1, condition2,...) ➔ the answer (True, False) Example: =OR(A1>50, B1="Yes") 4. NOT Function: Syntax: =NOT(logical) Example: =NOT(A1>50)