Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.
Formulas | Excel Automation with Python
Excel Automation with Python
course content

Course Content

Excel Automation with Python

bookFormulas

Formulas are mathematical expressions or equations that can be used to perform calculations on data in Excel worksheets. The openpyxl library in Python provides methods for creating and manipulating formulas in Excel files, allowing you to automate complex calculations and data analysis tasks using Python. With openpyxl, you can apply virtually any formula that is supported by Excel itself. The library allows you to set a cell's value to a formula by entering the formula as a string, prefixed with an equal sign =. When the Excel file is opened, the formula will be evaluated by Excel, and the result will be displayed in the cell.

This ability includes, but is not limited to:

  • Arithmetic operations: =A1 + A2, =A1 * A2, etc;
  • Statistical functions: =SUM(range), =AVERAGE(range), =MIN(range), =MAX(range), etc;
  • Text manipulation: =CONCATENATE(A1, B1), =LEFT(text, num_chars), =UPPER(text), etc;
  • Date and time: =TODAY(), =NOW(), =DATE(year, month, day), etc;
  • Lookup and reference: =VLOOKUP(value, range, col_index, [range_lookup]), =INDEX(range, row_num, [col_num]), =MATCH(value, range, [match_type]), etc;
  • Logical operations: =IF(condition, value_if_true, value_if_false), =AND(logical1, [logical2], ...), =OR(logical1, [logical2], ...), etc.
Task
test

Swipe to show code editor

  1. Add a formula in cell "P2" that will calculate the average from cell "H2" to "H31" ("=AVERAGE");
  2. Save the workbook.

Mark tasks as Completed
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Formulas are mathematical expressions or equations that can be used to perform calculations on data in Excel worksheets. The openpyxl library in Python provides methods for creating and manipulating formulas in Excel files, allowing you to automate complex calculations and data analysis tasks using Python. With openpyxl, you can apply virtually any formula that is supported by Excel itself. The library allows you to set a cell's value to a formula by entering the formula as a string, prefixed with an equal sign =. When the Excel file is opened, the formula will be evaluated by Excel, and the result will be displayed in the cell.

This ability includes, but is not limited to:

  • Arithmetic operations: =A1 + A2, =A1 * A2, etc;
  • Statistical functions: =SUM(range), =AVERAGE(range), =MIN(range), =MAX(range), etc;
  • Text manipulation: =CONCATENATE(A1, B1), =LEFT(text, num_chars), =UPPER(text), etc;
  • Date and time: =TODAY(), =NOW(), =DATE(year, month, day), etc;
  • Lookup and reference: =VLOOKUP(value, range, col_index, [range_lookup]), =INDEX(range, row_num, [col_num]), =MATCH(value, range, [match_type]), etc;
  • Logical operations: =IF(condition, value_if_true, value_if_false), =AND(logical1, [logical2], ...), =OR(logical1, [logical2], ...), etc.
Task
test

Swipe to show code editor

  1. Add a formula in cell "P2" that will calculate the average from cell "H2" to "H31" ("=AVERAGE");
  2. Save the workbook.

Mark tasks as Completed
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Section 1. Chapter 9
AVAILABLE TO ULTIMATE ONLY
We're sorry to hear that something went wrong. What happened?
some-alt