We’re wrapping up a month of blog posts on policyIQ reports by saving the “scariest” topic for last: exporting reports from policyIQ to Microsoft Excel. We receive a number of emails from users asking for help formatting or adjusting their exported reports – so here are seven of our top tips!
1. Remove the first row of merged cells
When exported into Excel, row 1 will always show you the Name of the Report. This is great information, but the merged cells in that first row will make it difficult to sort and filter your content. Instead, save your report using this name – and then remove that first row to give yourself the flexibility to sort, filter and rearrange your data more easily!
2. Automatically Resize Rows
The cells in your exported report results will be automatically formatted to “wrap text” – but they will be sized to only display one row. To show the full descriptions (such as a full “Project Task Description”), highlight the entire worksheet and then place your mouse on the line between any two row numbers. Double-click your left mouse button, and the rows will be automatically resized to show all of the text in each cell.
You might find that this makes your cells far too long if your text in any column is extensive. In that case, you may prefer to repeat the highlight of your entire sheet, and instead resize a single row to what might be a typical size. That will resize all of your rows to an identical height.
3. Remove dark row separators and replace with standard Excel gridlines
I don’t know about you, but I’m not a big fan of heavy outlines around the cells or rows in my Excel spreadsheets. I like to keep things simple and rely instead on the standard gridlines that Excel so conveniently provides. If you are like me, you can easily remove the borders with a few simple steps:
A. Highlight the entire worksheet.
B. Go to your toolbar and find the icons for your borders. Select the option for “No borders”.
C. If you want to add in the gridlines, you can go to View in your toolbar (or in your ribbon, for those Office 2007 users), and choose to view the gridlines.
D. You also need to remove the white “fill” in the background before those gridlines will appear. Highlight the sheet, and go to the Fill icon and choose “No Fill”.
4. Remove any undesirable HTML ‘tags’
Some of you may have found that when you export content into Excel, some characters in your text are appearing as the coded HTML tags rather than the characters themselves. Some examples might be dashes, which appear as ‐ rather than simply “-“, or quotes which appear as ". The explanation of why this happens could be a blog post in itself, but the bottom line is that it displays properly in the policyIQ pages, but MS Excel doesn’t interpret those correctly as the characters you intended.
There’s an easy fix, though! Highlight your entire worksheet and do a Find / Replace. (Just hit Cntrl F on your keyboard.) Enter ‐ as the text to find, and – as the text with which to replace it. Click Replace All and you’re done!
5. Create hyperlinks in the “Item URL” column
You have the option of exporting the “Item URL” as one of the columns in your report results, however when exported into Excel, this is formatted as simple text. This means that it does not automatically launch a user to policyIQ, but rather has to be copied and pasted out into the browser.
Insert a new column in your report results, and use this simple formula to adjust text into hyperlinks:
B2 is simply the cell that contains the URL. Copy the formula the entire way down that new column to the bottom of your results, and now all of your rows have an active hyperlink to launch to the policyIQ page.
6. Separate a Multi-Select field into separate columns with an X to fill in the blanks
Do you have a multi-select field in your content somewhere that you’d prefer to pull apart into its component answers in your report results? A good example of this might be the Financial Statement Assertions on your Risks and/or Controls. You probably have a multi-select field, but you might find it more convenient to evaluate your data if you can pull those apart to see X’s in individual columns where that Financial Statement Assertion applies. There are a couple of ways to go about it, but based on my own trial and error experience, I’ve found that the following process works great.
A. Add columns to your report results worksheet – one column for each of the Financial Statement Assertions (or other multi-select value that you wish to separate out).
B. In each of those columns, you be using a formula to determine if that specific Financial Statement Assertion is included for this row. That formula is:
“Completeness” is the value or text that you are looking for.
I2 is the cell in which you are looking for that value – or the original column of data from your policyIQ report results.
“X” is the text that you wish to see in the new column if the text is found.
You’ll notice that the last item in the formula is “” – indicating that if the value is not found, no text should be entered.
And remember that you can always copy a formula the entire way down the column in your spreadsheet simply by dragging the lower right corner of any cell. The formula will automatically adjust to look for the correct cell, such as I3, I4, etc in our example.
7. Remove duplicated / repeated content in your Detail Link Report (ie “Risk Control Matrix”)
A Detail Link Report shows the relationships between content. The most common use of this type of report is a Risk Control Matrix, so you may show the Risks in the first column, and then all of the linked Controls in the second column. If a single Risk has five linked Controls, the exported Excel version of the report will repeat the Risk information five times (into five rows) followed in each row by one of the Controls.
You might prefer to see blank cells in those first columns if the Risk is repeated, so here is an easy way to identify which Risks ARE repeated and delete all of those repeats:
A. Add a column to your report beside either the Page ID for your Risks. (If you haven’t included Page ID in the report results, you can use Reference Number or Name – whatever is always unique for each Risk.)
B. In that new column, enter the following formula to determine if the Risk is a duplicate of the line above it: =IF(A2=A1,”Duplicate”,””)
A2 is the cell representing the Page ID (or Reference Number or Name) of the Risk in that row, while A1 represents the Page ID of the Risk in the row above it.
Duplicate will appear if the two cells are identical, meaning that the row is a duplicate and the data can be cleared.
C. Once you have identified all of the duplicate rows, you can sort on that row – and then highlight all of the duplicate cells, right-click and choose to “Clear contents”.
D. Do not simply delete the rows! Remember that you do want to see the Control information in the columns further to the right in the spreadsheet. You just want to clear the duplicate contents.
policyIQ Reports module gives you the power and flexibility that you need to pull out data for analysis or presentation, but we know that many of you rely on the formatting capabilities of Excel to create the great looking reports that you distribute externally. We hope that these tips can make those formatting tasks a bit quicker! As always, if you have any questions, please contact our support team.
What tips do you have for formatting your reports? Any ideas that you want to share in comments that help you to create spectacular report results?