Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a linked table in my PPT (PowerPoint 2010), which is from a portion of an Excel 2010 worksheet. When I update the data in Excel, the table updates automatically in PPT, which works fine and as expected.

Now, the problem is how to style this linked table using PowerPoint 2010's built-in styles. So when you insert a regular PowerPoint table, you have two tabs on the ribbon under Table Tools: Design and Layout. Under Design tab, in the Table Styles section, you can quickly style the table using a bunch of built-in table styles.

However, when I have a linked table from Excel, I don't have those options: when I select the table, only one tab appears under Table Tools on the ribbon, which is Format. And I can't use any of the built-in table styles of PowerPoint. This makes the linked table inconsistent in terms of style, comparing to other regular PowerPoint tables.

Can someone please shed a light on this? Many thanks!

share|improve this question

3 Answers 3

Unfortunately, the built-in styles are only available for styling tables created in PowerPoint rather than linked or pasted in.

A kludge that may or may not produce acceptable results for you would be to create an empty PowerPoint table in the style of your choice and then overlay a linked Excel table. A bit of back-and-forth adjustment between Excel and PowerPoint can produce a reasonable alignment, font match, etc. The graphic shows the results of a very quick mockup that I did.

enter image description here

share|improve this answer

The thing about linked content in general is that PowerPoint has no idea what it is; it has a picture of the content (handed to it by the application that produced the content ... Excel in this case), and a link to the file that contains the actual data. All it can do for you if you want to edit the content is to activate it in the original app.

Your best bet may be to format the table in Excel to begin with.

If you're trying to match a specific PPT style, create a table in PPT, copy/paste it into Excel. You'll want to fill all the cells with text first, else Excel will apply its default text formatting to empty cells. Adjust the column widths as needed, add your real data and save the file. Then link from it back to PPT.

share|improve this answer
up vote 0 down vote accepted

Thanks for the responses. But, it looks to me that the best solution is still to style the table in PPT and edit your data in Excel. So I wrote a small piece of VBA code to transfer data from Excel to PPT and it works perfectly for me.

Here is the code:

Option Explicit
Option Base 1

Const WB_FINANCIALS As String = "\CPMR Financials.xlsm"
Const WS_SALES As String = "Sales Forecast"

Const SLIDE_SALES_FORECAST As String = "SalesForecast"

Const TABLE_SALES_FORECAST As String = "TableSalesForecast"

Const R_CPMA_COM_SALES As String = "cpma_complete_sales"
Const R_CPMA_LARGE_ATC_SALES As String = "cpma_large_atc_sales"
Const R_CPMA_REGULAR_ATC_SALES As String = "cpma_regular_atc_sales"
Const R_CPMA_REPORTS_SALES As String = "cpma_reports_sales"
Const R_RXR_REPORTS_SALES As String = "rxr_reports_sales"
Const R_TOTAL_SALES As String = "total_sales"


Sub ReadFinancialsBook()
    Dim XLApp As Excel.Application
    Dim FBook As Excel.Workbook
    Dim SalesForecast As Excel.Worksheet

    Set XLApp = Excel.Application
    Set FBook = XLApp.workbooks.Open(FileName:=ActivePresentation.Path + WB_FINANCIALS, ReadOnly:=True)
    Set SalesForecast = FBook.Worksheets(WS_SALES)

    Dim PPT As Presentation
    Dim SalesSlide As Slide

    Set PPT = ActivePresentation
    Set SalesSlide = PPT.Slides(SLIDE_SALES_FORECAST)
    Call ReadSalesForecast(SalesSlide, SalesForecast)

    Set SalesSlide = Nothing
    Set PPT = Nothing

    FBook.Close
    Set SalesForecast = Nothing
    Set FBook = Nothing
    Set XLApp = Nothing
End Sub

Private Sub ReadSalesForecast(S As Slide, WS As Excel.Worksheet)
    Dim T As Table
    Set T = S.Shapes(TABLE_SALES_FORECAST).Table

    Call WriteWSRangeToTable(T, 2, 4, WS, R_CPMA_COM_SALES)
    Call WriteWSRangeToTable(T, 5, 4, WS, R_CPMA_LARGE_ATC_SALES)
    Call WriteWSRangeToTable(T, 8, 4, WS, R_CPMA_REGULAR_ATC_SALES)
    Call WriteWSRangeToTable(T, 11, 4, WS, R_CPMA_REPORTS_SALES)
    Call WriteWSRangeToTable(T, 14, 4, WS, R_RXR_REPORTS_SALES)
    Call WriteWSRangeToTable(T, 17, 4, WS, R_TOTAL_SALES)

    Set T = Nothing
End Sub

Private Sub WriteWSRangeToTable(T As Table, row As Integer, col As Integer, WS As Excel.Worksheet, Name As String)
    Dim i, j, r, c As Integer
    Dim str As String

    r = WS.Range(Name).Rows.Count
    c = WS.Range(Name).Columns.Count

    For i = 1 To r
        For j = 1 To c
            str = CStr(WS.Range(Name).Cells(i, j))
            str = Format(str, "#,##0")
            T.Cell(row + i - 1, col + j - 1).Shape.TextFrame.TextRange.Text = str
        Next j
    Next i
End Sub
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.