Using Excel formulas in Word Document

-

Method 1: Direct input of Formulas in a table in word document


1) Create a table and insert required data into it



2) In our example, we need to have a formula in the last column, it should be a multiplication of the 2 columns to its left. To do this keep the cursor on the cell which needs the formula then go to to "Layout" tab at the top and click on the button "Formula"






3) In our example, we can calculate the product using the following 2 formulas:

  • =PRODUCT(LEFT)
  • =C2*D2

Quick Notes:

  • The word (LEFT) in the above Formula indicates that the formula will be applicable on all cells to the left of the cell where the formula is applied.
  • The Cells can also be referred to how they are referred to in excels starting with A1 referring to the cell in the first column and the first row.
  • The list of all possible formulas can be seen in the drop-down under "Past Function"
  • In case of any value change in cells of the table, the formula will not auto-refresh like in excel, to refresh the formula, right-click on the cell and click "Update Field" or use shortcut F9. For a quick refresh, Select all the contents of the document using "Ctrl + A" and then hit F9.
  • The Formula will have to be applied on each cell of the column as described above and does not auto-apply to all the rows in a column as in excel.

More details are at the below link:



Method 2: Embedding an Excel table in a Word document

This approach is to be used in the case of complex formulas used in excels when the required table is already existing in excel and we need to have it included in a word document along with the formulas for editing it later. 

1) Copy the table from Excel and paste it into the Word document using the "Paste Special" Option: 



2) In the Pop-Up that comes, select the option "Microsoft Excel Worksheet Object"



3) The Result is as shown below:



4) To edit the data in the table, double click on the table and it opens up an excel editor to make the required changes.



Note: Initial idea/thought for this post is from the below link
https://www.makeuseof.com/use-excel-formulas-in-word-documents/ 

Comments

Post a Comment

Popular posts from this blog

SAP ABAP - Generating Excel file for downloading or sending over email

-
Creating excel content from data in an internal table?  Getting the following message when opening an excel created out of SAP - "The file you are trying to open, ‘filename.xls’ is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" Below is the best solution. This solution is based on the approach used by standard SAP to created excel content from ALV. Once any ALV is displayed, you can get the data into excel using the menu bar option LIST -> EXPORT -> SPREADSHEET.  If there are any questions, put them in the comments.  Sample working code: *&---------------------------------------------------------------------* *& Report Y_EXCEL *&---------------------------------------------------------------------* *& *&---------------------------------------------------------------------* REPORT  Y_EXCEL ...
Read more

Get your signature from a paper to word document

-
Image
Follow the below steps to your signature from a physical hard copy onto a word document. Also ensuring that the background matches. Step 1: Put your signature on a piece of paper and take a snap of it with your mobile camera. Below is what you will have    Step 2: Open a word document that would basically have contents of a letter that you are drafting needing your signature on it and paste the signature pic from step 1 in the appropriate location. Step 3:  The next step is to ensure that the background of the signature is changed so that it matches with the rest of the content on the word document. Right-click on the image and select 'Format Picture' Go to the option of 'Picture Corrections' and set the contrast as '100' The end result is the following:
Read more