Insert or Embed Excel File In Word, Excel Or PowerPoint Presentation - As Object

Today in this article I will discuss with you how to Embed Excel (as Object) in office tools, like Microsoft Word, Microsoft PowerPoint and in Microsoft Excel itself.

What is Embedding ?

Excel Object Linking and Embedding - Object Linking and Embedding (or OLE for short) is a technique used to insert data from one programme into another.

So what do I mean to say by 'Excel as Embedded Object' ?

This questions may arise in your mind, to understand what is 'Excel as Embedded Object' suppose you are in a meeting where you need to give a speech about your opinion or performance with the supportive data. The two things you required

1.   Power Point Presentation and

2.   Excel workbook with data

Now in an ongoing discussion/meeting, if your data in Excel need to display then you must minimize PowerPoint presentation and then you have to find out the specific folder where your excel is stored and then you open it to display the data.

Now, in another situation let suppose you need to email your presentation with the supporting Excel file you have to attach two files (*.ppt or *.xls).

Or suppose you need to update the Excel data and to be displayed in PowerPoint presentation then also you have to update and copy the data then paste this data in PowerPoint slide.

Using embedded object you can shorten this process and can accomplish the task in seconds.

Embed is a process where we insert a file or data from one application to another application, e.g., attach or insert an Excel file or data in PowerPoint presentation or Word file or in Excel.

You may think that you can put a link and that will also do the same job, i.e., it will also refer to the file too.

You are right but in Embedding, if you update your excel file it will easily change the data in the PowerPoint presentation.

Let us learn how to insert Excel in PowerPoint presentation and Microsoft Word.

Excel Workbook Embedded in Microsoft Word

How to insert Excel in Excel ?

Steps :

1.   On the Insert tab, in the Text group, click Object.

Object Menu in Excel

2.   In the Insert Object dialog box, select Create from file.

Select from File

3.   Click Browse, and in the Browse dialog box, browse to and then select the Excel workbook that contains the Excel file that you want to insert, and then click OK.

Select from File - Display as icon

4.   In the Insert Object dialog box, select 'Display as icon' and then click OK.

Embedded Excel Displaying as icon

Save this workbook.

Now whenever you double click on this ICON, it will open this workbook.

Note : The important thing to be noticed is that whenever you send this or share this Excel workbook, the attached or inserted Excel will also be shared.

How to insert or Embed Excel in Microsoft Word ?

Steps :

Same Steps to be followed,

1.   On the Insert tab, in the Text group, click Object.

Object Menu in Word

2.   In the Insert Object dialog box, select Create from file.

Select from File - MS Word

3.   Click Browse, and in the Browse dialog box, browse to and then select the Excel workbook that contains the Excel file that you want to insert, and then click OK.

4.   In the Insert Object dialog box, select 'Display as icon' and then click OK.

Excel file Embedded in MS Word

How to insert or Embed Excel in Microsoft PowerPoint ?

Steps :

Steps are same,

1.   On the Insert tab, in the Text group, click Object.

Object Menu in PowerPoint Presentation

2.   In the Insert Object dialog box, select Create from file.

Select from file - PowerPoint Presentation

3.   Click Browse, and in the Browse dialog box, browse to and then select the Excel workbook that contains the Excel file that you want to insert, and then click OK.

4.   In the Insert Object dialog box, select 'Display as icon' and then click OK.

Excel file embedded in PowerPoint Presentation

You might noticed that the process is same to embed Excel file (it can be any file, pdf, text etc.) in any office tool.

You might also noticed that I always used the option 'Display as icon' not the other option 'Link' or 'Link to a file'.

Let us understand what will happen if we use the 'Link' option.

Embed Excel in PowerPoint using Link to file.

I have done all the steps mentioned above and embedded the Excel file as Linked in PowerPoint and it is displayed as below.

Excel file embedded as LINK in PowerPoint Presentation

Link Excel file to PowerPoint Presentation

The existing table in the excel is displayed as 'table' in PowerPoint presentation, another noticeable thing is that if you double click this table this will activate the Excel and now if you update this table it will be updated in PowerPoint presentation also. (I am using Office 2007 so it will open the Excel workbook but in Office 2013 and after 2013) you can edit the table inside the PowerPoint presentation)

Link Excel file to Microsoft Word

The whole table will be embedded in the word file as 'Table' and you can also edit the excel inside the MS Word file. Please follow the below picture.

Excel file embedded in Word as LINK

Link Excel file to Excel

The Excel table will be displays as OBJECT in excel file. Please follow the below picture.

Excel file embedded in Excel as LINK

Hope you have enjoyed ... Please comment if you have any question or doubts in your mind...

Keep reading & Sharing

Stay blessed ...

Pivot Table Errors

PivotTable in Excel is an wonderful tool for Data Analysis. But we get few common annoying problem in PivotTable which we will solve in this article.

Few Excel problems which we encounter in PivotTable is listed below, we will discuss these points step by step.

1.   Column width changing when refreshed - prevent column width change

2.   New data not showing in pivot table

3.   PivotTable not Creating

4.   Old Items in the Drop Downs - Clear Old Items in PivotTable

5.   GetPivotData Formula - Remove GetPivotData from PivotTable

6.   Change Values from Count to Sum

7.   Show Zeros in Empty Cells

8.   Pivot Table automatically grouping dates into Year, Quarter, Month

Lets discuss...

1.   Column width changing when refreshed - prevent column width change

Steps :

1)          Right Click anywhere inside the pivot table

2)          Choose PivotTable Options

3)          In the PivotTable Options dialog, choose Layout & Format at the top, and the uncheck AutoFit column widths on update, then click OK.

Column width changing when refreshed - prevent column width change

Column width changing when refreshed - prevent column width change

2.    New data not showing in pivot table

This is because your newly added data is not selected by the PivotTable. You have to select the Range of data from PivotTable.

Steps :

1)          Select any cell in the pivot table

2)          On the Ribbon, click the Options tab

3)          Click Change Data Source select the complete data source range.

4)          Click OK

New data not showing in pivot table

3.  PivotTable not Creating

Please check you Data Source, to create PivotTable you need column name continuously - no blank column should exist between other columns.

4.   Old Items in the Drop Downs - Clear Old Items in PivotTable

Sometimes you will find that your PivotTable showing Old data in the filter although it is deleted from the source data.

To clear the old item from an existing PivotTable follow the following steps.

Steps :

1)          Right Click anywhere inside the pivot table

2)          Choose PivotTable Options

3)          Go to Data tab (as shown in the below picture)

4)          Change Automatic to None in the 'Retain items deleted from data source' section 'Number of items retain per field:' from the drop down button.

5)          Click 'OK'.

Old Items in the Drop Downs - Clear Old Items in PivotTable

5.   GetPivotData Formula - Remove GetPivotData from PivotTable

This was most annoying problem for me, you know sometimes we need to calculate beside PivotTable and for that we need to select the cells from the PivotTable - now when I select any cell after = , it always shows =GETPIVOTDATA("Target",$B$3,"Zone","North","Day","Friday").

Whereas I only need '=D7'. You can do it, you can stop GETPIVOTDATA, follow the following steps.

Steps :

1)          Select any cell in the pivot table

2)          On the Ribbon, click the Options tab

3)          Click on the drop down button in the left side Option button and

4)          Uncheck 'Generate GetPivotData'

GetPivotData Formula - Remove GetPivotData from PivotTable

6.   Change Values from Count to Sum

In a PivotTable you may need Count of items or Sum of Item. It is very easy to change a data value from Sum to Count or from Count to Sum, pleas follow the following steps.

Steps :

1)          Select any cell in the pivot table value column

2)          Right click and select 'Value field settings' from the pop up option.

3)          In the popup window you will see a list of functions, select as per your requirement and

4)          Click 'OK' to get the desired result.

Change Values from Count to Sum

7.   Show Zeros in Empty Cells

In PivotTable we get blank cells if there is blank in the source data, follow the following steps to get zeros in the blank cells in a PivotTable.

Steps :

1)      Select any cell in the pivot table

2)      Right click and select 'PivotTable Options...' from the pop up option.

3)      Enter  '0' or '-' or any other character in the box beside 'For empty cells show' as shown in the below picture.

Show Zeros in Empty Cells

8.   Pivot Table automatically grouping dates into Year, Quarter, Month - solved

If date is the in the source data and we insert PivotTable in Excel 2016 date field comes in Qtr grouping.

Solution : If you need the date field just select that cell and Right click on that cells, from the popup options select 'Ungroup' and you will see that the Grouping is broken and date appeared in the PivotTable.

Pivot Table automatically grouping dates into Year, Quarter, Month

I assume that you know how to create PivotTable in Excel, if you are not familiar with Pivot Table, you may read the following Articles .

Previous Articles : -

• Pivot Table in Microsoft Excel - Pivot Table Field List Report Functions of Filter Column Labels Row Labels Values

• Customize Pivot Table - Step by Step

• Pivot Table Calculated Field - Step by Step

• Pivot Table Calculated Field vs Calculated Item

Hope you have enjoyed ...

Keep reading & Stay blessed ...

DB Function

In this article we will discuss about DB Function, another financial function in Microsoft Excel.

Here 'DB' is not referring to Database, it is Depreciation in accounting. 

DB function  returns the depreciation of an asset for a given time period based on the fixed-declining balance method.

Before discussing DB Function let us first understand what is depreciation and how many types of depreciation in accounting.

What is Depreciation ?

By definition, 'Depreciation' is an accounting method of allocating the cost of a tangible asset over its useful life and is used to account for declines in value.

In simpler language 'Depreciation' is the value of an tangible asset after wear and tear.

What Are the Main Types of Depreciation Methods?

There are several types of depreciation expense and different formulas for determining the book value of an asset. The most common depreciation methods are...

1.   Straight-line (Its very simple in calculation, a fixed amount will be deducted every year till the 'Life')

2.   Fixed declining balance

3.   Double declining balance

4.   Units of production

5.   Sum of years digits

In this article we will discuss about Fixed Declining Balance method.

Syntax

The syntax for the DB function in Microsoft Excel is as under

=DB(cost, salvage, life, period, [number_months])

Parameters or Arguments

cost

The original cost of the asset.

salvage

The salvage value after the asset has been fully depreciated.

life

The useful life of the asset or the number of periods that you will be depreciating the asset.

period

The period that you wish to calculate the depreciation for. Use the same units as for the life.

number_months

Optional. It is the number of months in the first year of depreciation. If this parameter is omitted, the DB function will assume that there are 12 months in the first year.

How to use the DB Function in Excel?

To understand how to use DB function in Excel, let us take an example.

Example :

Suppose a machinery is purchased in this year and the related information are

1.   Cost of the machine : $100000

2.   Salvage Value : 10000

3.   Life of the machine : 5 years

4.   Period : 1, 2, 3, 4 & 5 (as the life of the machine is 5 years)

Here after implementing the formula for DB function the results are shown in the below image and the formula will be for the 1st year =DB(C3,C4,C5,C6) - the amount is $ 36900.

How to use DB Function in Excel

In next article we will discuss about the DDB function in Excel.

Note : The DB function uses the fixed-declining balance method to compute the asset’s depreciation at a fixed rate.

The formula used by DB to calculate depreciation for a period is,

=(Cost – Total depreciation from prior periods) * Rate

and the calculation of Rate is,

Rate = 1 – ((salvage / cost) ^ (1 / life)).

Different formulas are used for first and last periods.

For the first period, DB uses the formula

=Cost * Rate * Month / 12

For the last period, DB uses the following formula is 

=((Cost – Total depreciation from prior periods) * Rate * (12 – month)) / 12

Keep in mind you need to apply the same formula in excel. You don't need to calculate the above formulas.

Hope you have enjoyed ...

Keep reading and sharing ...

Take care...

NPER Function

In last article we have discussed about PMT Function to get the amount of the payment or premium or EMI per month.

But suppose we have the premium amount and other information but not the period (number of months) and we need to calculate the number of months then we need to use another formula to get the period. Microsoft Excel provides an excel function NPER Function which will help us to get the 'period'.

 Today in this article we will discuss about NPER function.

Suppose we have the following information, like Loan Amount, Interest Rate, Installment Amount and Compounding period per year and we need to derive the number of months from these information to replay the full amount with interest. In this situation we will use NPER Function.

So, let's see how to calculate the period (number of months) to repay the amount.

For Example let suppose we have the details with us,

1.   Loan Amount (suppose $7000)

2.   Interest Rate per annum (let it be 7.50%)

3.   Installment Amount ($150)

4.   Compounding period per year (suppose every month i.e., 12)

What does Compounding Periods Per Year mean?

The compounding period is the number of times that unpaid amount interest is added to the principal amount of the loan.

 In simpler words, it is the number of times a bank or lender calculates the due amount.

If  'n' be the Compounding Periods Per Year the

if interest is compounded yearly, then n = 1;

if semi-annually, then n = 2;

quarterly, then n = 4;

monthly, then n = 12;

weekly, then n = 52;

daily, then n = 365; and so forth, regardless of the number of years involved.

I think you got a clear idea about Compounding Periods Per Year, now we will move to the NPER Function.

Syntax

The syntax for the NPER function in Microsoft Excel is very simple,

=NPER( interest_rate, payment, PV, [FV], [Type] )

Parameters or Arguments

interest_rate

The interest rate for the loan.

payments

The amount of the payment for each period. This should be entered as a negative value.

PV

The present value or principal of the loan.

FV

It is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes a FV value of 0.

Type

It is also optional. It indicates when the payments are due. If the Type parameter is omitted, it assumes a Type value of 0. Type can be one of the following values:

0 = Payments are due at the end of the period. (default)

1 = Payments are due at the beginning of the period.

Example :

Let us calculate the amount payable in every month based on the above information.

The formula will be =NPER(C5/C7,-C6,C4).

One question may arise in your mind why we divide the interest rate by compounding periods per year ?

This is because we have taken the interest rate as per year and the lender calculates the loan payment amount per month. We need to break down the full year interest rate by the Compounding Periods per year.

The below image will clarify NPER function.

How to use NPER Function in Excel

Hope you have enjoyed ...

Keep reading & Stay blessed ...

PMT Function

In this article we will discuss about PMT function, one of the financial functions available in Microsoft Excel which calculates the payment for a loan based on constant payments and a constant interest rate. We use PMT function in Excel as formula to figure out a monthly loan payment.

Last year I have to take a loan from Bank for short period and I was trying to calculate the amount in Excel that I must pay per month for that borrowed amount.

Surprisingly, Microsoft Excel provides a very easy function, 'PMT function' which is very easy to calculate the amount payable for a loan.

Let us discuss about PMT function but before that let us first write down the information we have.

Information with us for a loan

1.   Loan Amount (suppose $7000)

2.   Interest Rate per annum (let it be 5.50%)

3.   Periods, the number of months (suppose 48 months)

4.   Compounding period per year (suppose every month i.e., 12)

Note : Out of these four available data or information, the first three are easily understandable but the fourth information needs to be clarified to some persons. Honestly speaking it is little confusing to a newbie.

What does Compounding Periods Per Year mean?

The compounding period is the number of times that unpaid amount interest is added to the principal amount of the loan.

 In simpler words, it is the number of times a bank or lender calculates the due amount.

If  'n' be the Compounding Periods Per Year the

if interest is compounded yearly, then n = 1;

if semi-annually, then n = 2;

quarterly, then n = 4;

monthly, then n = 12;

weekly, then n = 52;

daily, then n = 365; and so forth, regardless of the number of years involved.

I think you got a clear idea about Compounding Periods Per Year, now we will move to the PMT Function.

Syntax

The syntax for the PMT function in Microsoft Excel is very simple,

= PMT(interest_rate, number_payments, PV, [FV], [Type])

Parameters or Arguments

interest_rate

The interest rate for the loan.

number_payments

The number of payments for the loan.

PV

The present value or principal of the loan.

FV

It is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes a FV value of 0.

Type

It is also optional. It indicates when the payments are due. If the Type parameter is omitted, it assumes a Type value of 0. Type can be one of the following values:

0 = Payments are due at the end of the period. (default)

1 = Payments are due at the beginning of the period.

How to use PMT Function in Excel ?

Example :

Let us calculate the amount payable in every month based on the above information.

The formula will be =PMT(C5/C7,C6,-C4).

One question may arise in your mind why we divide the interest rate by compounding periods per year ?

This is because we have taken the interest rate as per year and the lender calculates the loan payment amount per month. We need to break down the full year interest rate by the Compounding Periods per year.

The below image will clarify PMT function.

PMT Function

I hope you have enjoyed reading this article ...

Keep reading & Sharing....
Stay blessed ...