Advance excel

Introduction

An advanced computer course delves into the intricacies of computer science, equipping students with specialized knowledge and skills to tackle complex challenges in the digital realm. This course typically builds upon foundational concepts in computer science and explores advanced topics such as algorithms, data structures, artificial intelligence, machine learning, cryptography, computer networks, and software engineering methodologies.

Basic formulas in excel

Excel is a powerful tool for performing various calculations and analyses. Here’s a list of some basic formulas commonly used in Excel:

    1. AVERAGE: Calculates the average of numbers in a range.

      • =AVERAGE(number1, [number2], …)

    2. MAX: Returns the largest number in a range.

      • =MAX(number1, [number2], …)

    3. COUNT: Counts the number of cells that contain numbers in a range.

      • =COUNT(value1, [value2], …)

    4. IF: Performs a conditional test and returns one value if the condition is true and another if false.

      • =IF(logical_test, value_if_true, [value_if_false])

    5. VLOOKUP: Searches for a value in the first column of a table array and returns a value in the same row from another column.

      =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

    6. HLOOKUP: Similar to VLOOKUP, but searches for a value in the first row of a table array and returns a value in the same column from another row.

      =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
      =LEN(text)
    7. COUNT BLANK :

      To count the number of blank cells in a range in Excel, you can use the COUNTBLANK function. Here’s how to use it:

      =COUNTBLANK(range)
       

    8. CONCATENATE: Joins two or more text strings into one string.

      =CONCATENATE(text1, [text2], ...)
       
    9. & operator: Provides a shorter way to concatenate text strings.
      =text1 & text2

    10. LEFT: Returns a specified number of characters from the start of a text string.

      sc=LEFT(text, num_chars)

    11. RIGHT: Returns a specified number of characters from the end of a text string.

      =RIGHT(text, num_chars)

    12. MID: Returns a specific number of characters from the middle of a text string.

      =MID(text, start_num, num_chars)

    13. LEN: Returns the length of a text string (number of characters).

      =LEN(text)

    14. LOWER: Converts text to lowercase.

      =LOWER(text)

Advance formulas

Advanced formulas in Excel are powerful tools that allow you to perform complex calculations, data analysis, and automation tasks. Here are some examples of advanced formulas:

  1. INDEX-MATCH
  2. VLOOKUP with approximate match
  3. SUMIF/SUMIFS
  4. COUNTIF/COUNTIFS
  5. IFERROR
  6. OFFSET
  7. INDIRECT
  8. CHOOSE
  9. TEXTJOIN

Cell Formatting

Cell formatting in Excel allows you to customize the appearance of your data, making it easier to read and understand. Here are some common formatting options you can apply to cells:

  1. Number Formatting
  2. Font Formatting
  3. Alignment
  4. Borders
  5. Fill Color
  6. Conditional Formatting
  7. Number Styles
  8. Cell Protection

Bill

To create a bill in Excel, you can design a simple template that includes all the necessary information such as item descriptions, quantities, prices, and totals.

  1. Open Excel
  2. Set up the layout
  3. Enter headings
  4. Enter billing information
  5. Calculate totals
  6. Add tax and discounts (if applicable)
  7. Finalize the bill
  8. Save the template
  9. Print or send the bill

salary slip

To create a salary slip in Excel, you can design a template that includes all the relevant details such as employee information, earnings, deductions, and net pay.

  1. Open Excel
  2. Set up the layout
  3. Enter headings
  4. Enter employee information
  5. Enter earnings and deductions
  6. Calculate net pay
  7. Format the salary slip
  8. Add additional details
  9. Save the template
  10. Print or send the salary slip

Sales report

Creating a sales report in Excel involves organizing and summarizing sales data to provide insights into performance, trends, and key metrics. Here’s a step-by-step guide to creating a basic sales report:

  1. Gather your sales data
  2. Open Excel
  3. Enter your sales data
  4. Format your data as a table (optional)
  5. Analyze your sales data
  6. Create charts and graphs (optional)
  7. Design your sales report
  8. Add additional sections (optional)
  9. Review and finalize
  10. Save and distribute the report

order report

Creating an order report in Excel involves summarizing and organizing order data to provide insights into order volume, trends, and other key metrics. Here’s a step-by-step guide to creating a basic order report:

  1. Gather your sales data
  2. Open Excel
  3. Enter your order data
  4. Format your data as a table (optional)
  5. Analyze your order data
  6. Create charts and graphs (optional)
  7. Design your order report
  8. Add additional sections (optional)
  9. Review and finalize
  10. Save and distribute the report

Conditional formatting

Conditional formatting in Excel allows you to apply formatting to cells based on specific conditions or criteria.

  1. Select the cells
  2. Open the Conditional Formatting menu
  3. Choose a rule
  4. Set the formatting options
  5. Preview the changes
  6. Adjust the rule (optional)
  7. Apply the formatting

short cut keys

Shortcut keys in Excel can significantly improve your productivity by providing quick access to various commands and functions. Here are some commonly used shortcut keys:

  1. Ctrl + C: Copy selected cells.
  2. Ctrl + X: Cut selected cells.
  3. Ctrl + V: Paste copied or cut cells.
  4. Ctrl + Z: Undo the last action.
  5. Ctrl + Y: Redo the last undone action.
  6. Ctrl + S: Save the workbook.
  7. Ctrl + N: Create a new workbook.
  8. Ctrl + O: Open an existing workbook.
  9. Ctrl + P: Print the workbook.
  10. Ctrl + A: Select the entire worksheet.
  11. Ctrl + F: Open the Find dialog to search for text or values.
  12. Ctrl + H: Open the Replace dialog to find and replace text or values.
  13. Ctrl + Home: Move to the beginning of the worksheet (cell A1).
  14. Ctrl + End: Move to the last used cell in the worksheet.
  15. Ctrl + Arrow Keys: Move to the edge of the current data region.
  16. Ctrl + Shift + Arrow Keys: Select cells to the edge of the current data region.
  17. Ctrl + Spacebar: Select the entire column of the active cell.
  18. Shift + Spacebar: Select the entire row of the active cell.
  19. Alt + E + S + V: Paste special.
  20. Alt + E + S + T: Transpose data.
Scroll to Top