Printing Excel Reports via a Word Document

Share

Facebook
Twitter
LinkedIn

Printing Excel Reports via a Word Document

Using Microsoft Excel & Word

This post will detail the process of establishing a simple database in excel and then linking that to a Standard Report in word and saving the data as a new Word file.

Why use Word?

Some organisations will only accept files in Word format and may have specific formats which are used internationally (International shipping I’m looking at you!)

In these cases although you may be able to setup an Excel file to look exactly like its Word equivalent. But if they wont accept it…

You have to change.

Requirements:

+ A database source in Excel
+ A Word file (to be used as a template, not to be confused with a Word template)

The Process

The process is simply a matter of:

+ Setup a Control sheet
+ Setup a Transfer Sheet
+ Setup a Word template
+ Run the Report

This process will be explained step by step with the help of a worked example:  2007/10 Sample or 1997/03 Sample

This tutorial will only be using the 2007/10 files as examples but feel free to follow along if you are using previous versions.

The 2007/10 Sample has been tested on both Office 2007 & 2010
The 1997/03 Sample has been tested on Office XP (and I make no claim that it will work in prior versions but it might/should)

Open the example workbook (Production records.xlsm from the above links) or your own data file.

Notice that there are 3 worksheets in the workbook:

+ Control:   The master sheet which allows selection of your filter or summation criteria and a button to execute a macro
+ Transfer: The transfer sheet, the entry or summation here will be transferred to word
+ Data:        The database

Setup a Control Sheet

The control sheet is a simple data validation or selection tool and a button which will run a macro.

It can be as simple or as complex as you need to make it.

Example

In the example above there is simply a Data Validation cell which is linked to a list of shipment numbers and a Button to run the reports VBA subroutine.

You can make yours as simple or as complex as you need to extract the data from your data source.

The Produce Word Report button is linked to the MergeMe VBA subroutine.

Setup a Transfer Sheet

The Transfer sheet requires 2 rows

Row 1: Has a list of field names, These will be used in Word later so use something meaningful.
Row 2: Has a list of the records which will be transferred to Word. The cells will contain sufficient formulas to extract the relevant records from the Data sheet using the Data validation on the Control Sheet.

You need to setup sufficient fields to ensure that all records required in Word are setup or retrieved.

The order of the fields isn’t important as the field names are used for the transfer not the order.

Also you don’t have to use all the fields in Word, but if the Field isn’t made here you can’t retrieve it later.

The format or layout doesn’t matter as this is controlled in Word.

Example

Notice on the Transfer sheet that the Top Row is a list of field names

The second row uses an Index(Match( )) combination to retrieve the relevant records from the Data sheet.

Save the Excel file.

In the sample file I have made a simple retrieval of a matching records and associated fields, but the Transfer sheet could have just as easily sumarised multiple rows of data from your data source.

Setup a Word template

Setup in word a file which will be used as a template for the import.

Leave gaps where your fields values will go.

Save the file

Example

Open the example file (Shipping Template.docx)

If this is the first time you have opened the example file it may prompt you

“Opening this file will run the following SQL File …”

This is ok so accept Yes

You can either accept that and then follow the links to connect the file to the Production records.xlsm file

It will then prompt you for the Data Table which in our case is Transfer$ ie: the Sheet Name with a $ sign at the end

If it didn’t prompt you above or you answered No to the “Opening this file will run the following SQL File …” prompt we will connect again later anyway.

Now setup the file in word with all the text graphics, lines colors etc required for your form/report.

Leave gaps for the fields which we will add next.

Adding fields

Use mail merge to open the data source (Production records.xlsm)

Goto Mailings, Select Recipients, Use Existing List…

Navigate to the Production Records.xlsm file after which it will prompt you for the table which in our case is Transfer$ ie: the Sheet Name with a $ sign at the end (as discussed above)

Move to the 4 missing Field Locations as per the following table and insert the Field Names using the

Mailings, Insert Merge Field tab

Insert Fields as per the following table and highlights above:

Location Field Name (from Production Records.xlsm)
Shipment No : Shipment_No
Material : Cargo
Tonnes : Tonnes

The other fields Destination, Form and Date have already been pre-entered and are shown in Blue

You can format the fields as required, select the entire field and change the font, colors etc to suit.

You can view the field values using the Preview Results Button, see below

Make any other changes to the file

Save the file as a Word File Shipping Template.docx (not as a Word Template *.dotx)

Close Word


Run the Report

In the Production records.xlsm file we will now link the macro to the button on the control sheet

We need to check 3 lines in the macro before we execute it.

Goto VBA using Alt F11

Select the Production Records.xlsm, Modules on the left and find the

Sub MergeMe() Subroutine on the right

Near the top of the subroutine are 2 lines which list both the file which Word will use as a template and what the new file will be saved as after merging.

‘ Setup filenames

Const WTempName = “Shipping Template.docx ‘This is the Word Templates name, Change as req’d

Const NewFileName = “New Certificate.docx‘This is the New Word Documents File Name, Change as req’d

Change these values as appropriate

The code will overwrite the existing output file if it exists so once executes save it to another name/location.

If you are using your own data file copy this subroutine to your own VBA Module and edit as above.

You can now go back to Excel (Alt F11) and execute the macro using the button on the control page.

You should now have a new file called New Certificate.docx in the same directory as the Sample files.

Future Extensions

The above macro which does this transfer is a simple and easily scalable to 50+ fields without any modification.

Future enhancements would be:

+ Sourcing the New Word File name from the Control sheet

+ Incremental numbering of the word document each time the transfer is done

+ Numbering of the word document based on a Field value each time the transfer is done

+ Export of Multiple records at one time

+ Conditional formatting in Word based on field values

+ Improved error checking

If you are interested I encourage you to modify and post these enhancements here for all to benefit.

Macro

You can copy the Macro into any Excel file and save it as an *.xlsm file and link it to a Button and be up and running in minutes

The macro has a very small number of changes that need making internally to work anywhere.

What have been your Excel to Word transfer experiences ?

What have been your Excel to Word transfer experiences, let us know in the comments below:

What do you think of this approach to data transfer ?

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

15 Responses to “Highlight Employees by Performance Rating – Conditional Formatting Challenge”

  1. Stephen says:

    While this might solve the question Shelly asked, there is another option that might be more useful - a pivot table could make a list of people who fall into the various categories, so, if you needed to simply see who got in the top bracket to give them a bonus, you would have that list

    Simply sorting by the rankings would work too, but you would knock them out of alphabetical order. 

  2. Darin Myers says:

    Normal
    0

    false
    false
    false

    EN-US
    X-NONE
    X-NONE

    /* Style Definitions */
    table.MsoNormalTable
    {mso-style-name:"Table Normal";
    mso-tstyle-rowband-size:0;
    mso-tstyle-colband-size:0;
    mso-style-noshow:yes;
    mso-style-priority:99;
    mso-style-parent:"";
    mso-padding-alt:0in 5.4pt 0in 5.4pt;
    mso-para-margin-top:0in;
    mso-para-margin-right:0in;
    mso-para-margin-bottom:10.0pt;
    mso-para-margin-left:0in;
    line-height:115%;
    mso-pagination:widow-orphan;
    font-size:11.0pt;
    font-family:"Calibri","sans-serif";
    mso-ascii-font-family:Calibri;
    mso-ascii-theme-font:minor-latin;
    mso-hansi-font-family:Calibri;
    mso-hansi-theme-font:minor-latin;
    mso-bidi-font-family:"Times New Roman";
    mso-bidi-theme-font:minor-bidi;}

     
    The solution I chose makes use of the percentile formula.
     
    The percentile formula returns the value representing the K-th percentile of a range of values. The range of values is the first criteria, and K is the second criteria in the formula.

    I applied Conditional Formatting according to the formulas in the order below:

    5%    =$C6>=PERCENTILE($C$6:$C$33,0.95)   Dark Blue
    15%  =$C6>=PERCENTILE($C$6:$C$33,0.85)   Light Blue
    65%  =$C6>=PERCENTILE($C$6:$C$33,0.1)     Green
    10%  =$C6>=PERCENTILE($C$6:$C$33,0.05)   Light Red
    5%    =$C6<PERCENTILE($C$6:$C$33,0.05)     Dark Red
     
    The issue I noted with this approach is that Zambi was not highlighted in my solution as it is in the solution provided. Unless I am mistaken, and I very well may be, the 10th percentile for this data set is at 2.21, so Zambi would fall above the 10th percentile with a PR of 2.3.
     
    The first step to this was figuring out the 'buckets'; what scores should fall into each range. In attempting to match the formatting of the spreadsheet, I determined the buckets below.
     
    5% = 95% to 100%
    10% = 90% up to but not including 95%
    65% = 10% up to but not including 90%
    10% = 5% up to but not including 10%
    5% = under 5%
     
    After that, it is a relatively simple matter to plug the necessary values into the conditional formatting formulas as shown above.

    One final consideration is that while the buckets above match the color banding on the spreadsheet, I believe that the original request suggests a different color banding with 6 buckets shown below.
     
    Top 5%    = 95 to 100%    Dark blue
    Top 10%  = 85 up to but not including 95%    Light blue
    Top 65%  = 35 up to but not including 85%    Green

    Bottom 10% = 10% down to but not including 5%   Light Red
    Bottom 5%   = 5% or under    Dark Red
     
    This leaves one final bucket of 10 to 35% (exclusive of both values) that is not highlighted and so would remain white.
     
    Thank you Chandoo and Shelly for an interesting and useful exercise. This is certainly a valuable technique to have in my reporting bag of tricks.
     

  3. PSG says:

    Use of PERCENTILE is a smarter way of doing it.  Below is my solution.
     
    First 5 % = Apply conditional formatting (Dark Blue) as highlight ">=" =PERCENTILE(C:C,0.95)

    Next 15% = Apply conditional formatting (Lighter Blue) as highlight between =PERCENTILE(C:C,0.95)-0.01 and  =PERCENTILE(C:C,0.8)

    Next 65% = Apply conditional formatting as highlight (Olive Green) between =PERCENTILE(C:C,0.8)-0.01 and  =PERCENTILE(C:C,0.15)

    Next 10% = Apply conditional formatting as highlight (Lighter Red) between =PERCENTILE(C:C,0.15)-0.01 and  =PERCENTILE(C:C,0.05)

    Bottom 5% = Apply conditional formatting (Red) as less than =PERCENTILE(C:C,0.05)

    • Shailesh says:

      I agree, this is a challenge faced by HR managers every year and use of percentile formulae is the most popular solution which permits further processing like making bell curve, applying increments based on segmentation etc.

  4. Mayank Bhatia says:

    Hi Chandoo,

    I came at the same solution as yours (not looking at yours first) but I have hard coded the conditions in the conditional formatting. For example:

    =AND($C6>=$D$10,$C6<$D$9)

    I have done the same thing 5 times for each condition.   This makes the formatting independent of the order of specification. I think it will work better across versions of excel.

    To copy the same thing in all sheets, Shelly can copy these formatted cells with format painter and apply it to the relevant cells in next sheet and so on! I know 700 sheets will be difficult but I dont know of any other way to apply conditional formating rules to the whole sheet.

      

  5. Sameer Srivastava says:

    First i have used percentile formula in the next column of "percentile Threshold" where E5, E6.. is input to colour code.
    The idea behind doing this is to replicate the formula for any range and any threshold

    =PERCENTILE($C$3:$C$30,1-E5)

    =PERCENTILE($C$3:$C$30,1-E6)

    =PERCENTILE($C$3:$C$30,1-E7)

    =PERCENTILE($C$3:$C$30,1-E8)

    =PERCENTILE($C$3:$C$30,1-E9)

    Now i have given logic to different employee by applying "if Formula"

    =+IF(J3>=$G$5,1,IF(J3>=$G$6,2,IF(J3>=$G$7,3,IF(J3>=$G$8,4,5))))

    where 'J"  referes to PR and "G" refers to percentile derived from above mentioned formula.
    once again it is replicable (just change reference points)

    Now comes the major part of Conditional Formatting, i have used "use a formula to determine which cells to be formatted" 
    Formula =$j=5, format "required colour" Applies to "$I$3:$J$30" 
    plus put tick on stop if true

    This solves the query, important point that this is repeatable and can be done for n number of departments

    Thanks !

  6. Deepa says:

    I had done some reading on it and in Excel 2010 a new function has been introduced, percentile.exc. Attaching a video which also talks why the old percentile function shouldn't be used as it acts erroneous at times. Might be worth a watch Chandoo,
    http://www.itechtalk.com/thread10579.html

    • Hui... says:

      @Deepa

      Quit correct.

      Where ever you use statistical spreadsheet functions and are using excel 2010 you should use the new versions of the functions as MS did a lot of work to speed up and fix errors in the old functions.

      Warning: If you use the new Excel 2010 statistical functions in Named Formulas most of them will crash excel so do keep that in mind.

  7. Kishore says:

    Hello Chandoo,
    When i first read the challenge file, i thought, the color that need to be applied for a given rule, also need to be picked dynamically as given in rule set. But in the solution file, i found that color is hard Coded. So in case, someone has same data, but wants different colors, he/she needs to goto manage rules and change colors.
    Let me know if my understanding is correct, and if yes, can we also make the color to be applied dynamic?
     
    Thanks
    Kishore

  8. Roger L Moreno says:

    HI I ALSO USED THE PERCENTILE FUNCTION. HOWEVER, I WENT A STEP FURTHER AND USING THE SMALL() FUNCTION I SORTED THE DATA BY PERCENTILE SO THE COLOSCHEME WOULD BE GROUPED BASED ON THE VALUE. THIS WAY IT IS BETTER AND EASIER TO VIEW.

  9. [...] recently posted a challenge to help a reader with a [...]

    • Balraj says:

      Hi, i have got doubt regarding to the percentages that has been put in chandoo's spreadsheet, i cant understadn how he put directly. can some one please explain how chandoo put the percetages straight way that i stated below..

      5%

      15%

      60%

      10%

      5%

  10. I have stumbled on this post as the solution has been already given so I have taken the liberty to record a video where I show the implementation of it as well as adding a filtering feature which I hope can prove to be useful.

    Thank you

    http://www.xlninja.com/2012/06/28/how-to-use-excel-to-highlight-employee-performance-rating/  

  11. [...] scriu nici macar un cuvant din urmatorul articol. Astazi mi-am citit mailul si hopa challenge de la Chandoo. Cum puteam sa refuz asa ceva si m-am apucat de citit, iar dupa 5 min i-am spus sotului ca pe asta [...]

  12. Yves S says:

    Question for Chandoo:
    I came to your site late but am totally loving these challenges 🙂

    I guess it all boils down to how the bins are set up.
    I agree with the PERCENTILE.INC function.

    pls help me understand where I am wrong.

    I have determined following the bins:

    bottom 5% <=2.00 (F6:F33 <=PERCENTILE(range,.05))
    lower 15% (5+10) <= 2.40 (F6:F33 <=PERCENTILE(range,.15))
    lower 80% (5+10+65) <=3.46 (F6:F33 <=PERCENTILE(range,.80))
    lower 95% (5+10+65+15) <=4.00 (F6:F33 =PERCENTILE(range,.95))
    top 5% <=4.20 (F6:F33 <=PERCENTILE(range,1.00))

    I find that only Tom is highest scorer and unique top 5% achiever.

    I notice that Chandoo has included Christy and Daniel in top 5% achievers. How can there be 3 people in top 5% out of a population of 28 (5% of 28 = 1.4, i.e. only one person can achieve that status)?

    I tried different ways but cannot get to that distribution.

    Rest of the work is simply organizing the conditional formatting rules with Stop If True box checked.

    Thanks for your insights

Leave a Reply