Python in Excel: A Comprehensive Cheatsheet

Python in Excel Cheatsheet

Python Tools

• Formulas: Create Python formulas by clicking "Insert Python" or use the shortcut CTRL+ALT+SHIFT+P.
  • Thoughts: This allows the integration of Python scripting directly within Excel, providing enhanced data processing capabilities.

Referencing Data

• New xl function: Use xl("reference range, Table, Query etc.") to reference data.
  • Thoughts: This function helps fetch various types of data sources effectively including tables and queries.

Data Sources

• Excel Tables: df1=xl("Table[[All]]", headers=True)
• Dynamic Arrays: df2=xl("Sheet1!F4#", headers=True)
• Defined Names: df4=xl("RngData", headers=True)
• Power Query: df3=xl("QryData", headers=True)
• Cell Ranges: df5=xl("Sheet1!A3:D8", headers=True)
  • Thoughts: Different methods to import data into Python in Excel. Using Excel Tables and Dynamic Arrays can make data handling more structured.

Output Types

• Python Object: Dataframes, lists, etc in a single cell.
• Excel Value: Text, numbers, charts, etc. spill results to cells.
• Setting Output: Choose the format from the drop down or use the shortcut CTRL+ALT+SHIFT+M.
  • Thoughts: You can decide how the outcome of the Python code is represented in Excel cells.

Calculation Order

• Python formulas in Excel follow the top-left to bottom-right order when calculating cells and sheets.
• Python in one cell (A1) cannot reference another Python formula in cell (A11).
  • Thoughts: Understanding this order is crucial for structuring how calculations are performed and ensuring accurate results.

Recalc

• Recalculate Options:
  • Automatic: Python formulas recalculate sequentially in a Python cell chain.
  • Manual or Partial Calculation: Speed and improve performance, especially with high data.
• Tip: Use F9 for recalc.
  • Thoughts: Managing recalculations is important for optimizing performance in complex workbooks. Manual recalculation can save time in large data sets.

Excel Versions

• Available for Excel Desktop for Windows for Microsoft 365 Insiders Beta Channel users.
• Version 2309 Build 16.0.16818.20000 or later.
  • Thoughts: Limited to specific versions ensures compatibility and stability with new features.

Limitations

• Python animated charts not supported.
• Cannot connect to Power Pivot data.
• Not available for Windows Excel Beta versions yet.
• Case sensitivity in function names.
• Recalculation conflicts with certain formula behavior.
  • Thoughts: Be aware of these limitations to understand the current capabilities and plan your tasks accordingly.

Libraries Supported

• ANACONDA: Includes libraries such as pandas, statsmodels, matplotlib, NumPy, Seaborn, SciPy.
  • Thoughts: Leveraging the ANACONDA distribution broadens the functionalities due to the vast available libraries.

Errors

• Error Types:
  • #PYTHON!: Likely a syntax error.
  • #CONNECT!: Issues with Python cloud service.
  • #CALC!: Too much data.
  • #BUSY!: System busy.
  • #SPILL!: Data occupying cells.
  • #VALUE! or #UNKNOWN!: Python isn't available.
  • #TIMEOUT!: Network issues or excessive data/API throttling.
  • Thoughts: Recognize these errors to troubleshoot and resolve issues promptly.

Download More Info

• High-Res Version & More Info: Visit bit.ly/excelpy
  • Thoughts: Additional resources can provide more in-depth guidance.

Career Transforming Skills

• Consider further training with provided resources.
  • Thoughts: Upskilling with these tools can enhance career prospects and efficiency in data management tasks.

Reference: