techwise group logo

Excel Tips and Features: Data Basics with Python – Tech Tip for November 12, 2024

This week’s Tech Tip covers the basics of inserting Python and demonstrates a simple Python formula for Excel calculations. Python in Excel is now generally available within Excel for Windows (for Enterprise, Business users, and in preview for most non-commercial and Education users). Be sure to follow along with our previous Tech Tips to discover more Python in Excel tips. Let’s dive in!

Python Basics in Excel

Let’s begin with a walk through on the basics of inserting Python into Excel.

Getting Started: Insert Python in Excel

Open an existing workbook. Review our previous Tech Tips to create data with Copilot in Excel.

  • Select a cell, go to the Formulas tab, and click ‘Insert Python.’
  • Alternatively, type “=PY” in a cell, select ‘PY’ from the AutoComplete menu, or add an opening parenthesis: “=PY(“.

Combine Python with Excel Cells and Ranges

Easily work with Python to reference and combine specific cell data, let’s try this now using a quick ‘x + y’ example and walk through how to reference cells:

  • If you have not already enabled Python, select an open cell, and using the instructions above, click ‘Insert Python’. In our example, we ‘Insert Python’ into cell ‘H3.’

TIP: The cell you choose to ‘insert Python’ into will be the Python DataFrame and is the default cell where your calculated sum data from your formula will appear.

How to Reference Cells Using Python Code:

When referencing different data or cells in Excel, use the “xl()” function to interface between Excel and Python.

For example:

  • To reference cell ‘A1’, type “xl(“A1”)” in the Formula bar.
  • To reference a ‘range’ of cells, type “xl(“B1:C4”)” in the Formula bar.

Try a Basic Python Formula Calculation:

For a quick Python formula, type the following to achieve calculation or ‘sum’ results:

  • Insert Python and click into the Formula bar when ready.
  • Our next step is to define our cells that we’ll reference in our Python code.
    • Line 1 is the first cell reference for the calculation: type “x = xl(“D2”)”.
    • Line 2 is the second cell reference for the calculation: type “y = xl(“D14”)”.
    • Line 3 is the final part of the calculation to achieve the sum: type “x + y”.
  • Select the ‘checkmark’ on the Formula bar (at left). Alternatively, use the ‘CTRL’ + ‘Enter’ keyboard shortcut to execute your Python code.
  • The cell should now show the correct sum for the formula calculation.
    • In our case, cell ‘D2’ is equal to ‘900’ and cell ‘D14’ is equal to ‘1200.’
    • When we perform addition on these two numbers, the sum is equal to ‘2100.’
    • This amount is added to the cell you selected to ‘Insert Python’ into.

TIP: To reference Excel objects in a Python cell (if your workbook is in Protected or in Read-only mode), ensure the cell is in ‘Edit’ mode.

TIP: For editing your Python code, use the Formula bar. Expand the Formula bar by selecting the down arrow ‘v’ (to the right of the bar), or simply use the keyboard shortcut ‘CTRL’ + ‘Shift’ + ‘U’ to expand the view.

With Python integrations in Excel, users can automate intricate tasks and derive more profound insights from their data. We hope you’ll give this basic Python in Excel example a try!

Reach Out to TechWise Group

Interested in learning more about Excel, Python in Excel, and Copilot features? Reach out to TechWise Group. We’ll help you make the most of your Microsoft tools.

Important Microsoft Announcements:

  • Maximize your organization’s Security with Secure Score and MFA:
    • Get to know your Secure Score—your essential tool for proactive threat management.
    • Enabling Multi-Factor Authentication (MFA) is the best way to prevent cyberthreats.
  • Give your business a productivity boost with Microsoft Copilot:
    • Take advantage of a 15% discount on Copilot licensing now through Dec 31, 2024 (terms and conditions apply).
    • Get started with Copilot consulting services to organize and secure your data, while also training you and your team.
  • Keep your infrastructure up to date:
    • SQL Server 2014 reaches End of Support (EOS) on July 9, 2024.
    • Windows 10 reaches End of Support (EOS) on October 14, 2025.
    • Windows 11 feature update 23H2 is now available (September 2024).
    • Windows 11 version 22H2 Home and Pro editions will reach end of service (October 8, 2024) and will be required to upgrade to version 23H2 to continue receiving security updates.
    • Windows 11 Enterprise and Education editions with 22H2 will continue to be supported after October 8, 2024.
  • Contact TechWise Group to get started or to put a plan in place.