Article Archive Access – have we already solved your Excel issue?

The Excel Community web page includes a few ways to search for articles. From the main community page, the ‘Key resources and support section’ includes a link to current articles arranged in reverse chronological order, but it is the Archive link that allows you to search for articles on a particular topic:

The Archive section, ‘See more’ button displays not only a text-based search capability for articles that are part of the pre-September 2020 archive, but also a Power BI portal that is designed to provide a more graphical way of searching across both the archive and the articles on the current platform:

The use of Power BI to create the portal was partly an ‘eating our own dog food’ challenge. Given that Power BI is an increasingly important application for working with data stored in a range of formats, including spreadsheets, it seemed worth seeing how it could help provide easier ways to use the article knowledge base to solve members’ Excel problems and issues.

As part of creating the portal, we published a series of articles on how we did it. From the embedded portal page shown above and at the foot of this post, you can click on ‘Archive project’ in the Series/Topics section to display just the relevant articles in the list of posts:

Hovering over any of the Post titles in the list will display the post summary and, to view the post itself, you just need to click on the icon in the Link column.

Note that, because we have tried to cram everything into a single report page, you might need to use the ‘Open in full screen mode’ button at the bottom right of the embedded report to make everything easier to see. The keyboard Esc key will exit full screen mode and return you to the original web page.

The portal provides several different ways to search for the information that you are after. On the left-hand side of the page, we have included two ‘word clouds’. The Keywords word cloud is based on a list of words and short phrases that we have allocated to each of the articles, although it’s worth noting that some earlier articles are yet to be retrofitted with keywords and some of the other attributes. The Summary word cloud is based on all the post summaries. Just like the previous Series/Topic example, clicking on a word or phrase in either word cloud will filter the list of articles to display matching posts.

Again because of text size issues, we have created a separate report page for the Keyword Search. This can be accessed by clicking on the button just underneath the Keyword word cloud and allocates much more room to the word cloud:

In this screenshot we have clicked on the Power_BI keyword. Note that you can click on any of the other, greyed-out, keywords to change the filter directly, or you can click within the word cloud graphic, but outside of any existing words, to cancel the filter entirely. You can navigate back to the main page using either the link at the bottom of the page or by selecting it in the Pages pane at the top left.

On the main page, to the right of the word clouds we have a graphic that allows you to scroll through lists of Contributors and Authors and to select whichever you want to use to filter the list. Note that, because so many different authors have contributed posts over the years, we have limited the main display of authors to those that have submitted the most articles. However, a ‘Next level’ button (double downward arrows) just to the right of the Authors title will display the full list of authors, with a Drill up button then available to restore the main Authors display.

Just below the list of posts, there are two column charts. The one to the right displays numbers of posts by year and allows the list of posts to be filtered by year. The one to the left is based on the categories of user from the ICAEW Spreadsheet Competency Framework publication, allowing you to filter articles by user level. The post title backgrounds in the list are also shaded according to this categorisation and act in a kind of ‘peril-sensitive’ way to make the text of the article title increasingly difficult to read as the level of complexity increases…

There are also two more recent additions. In the top left-hand corner is a ‘Bookmark’ section that allows you to select posts from the previous two months using the Recent button, or to display posts relevant to the Twenty Principles for Good Spreadsheet Practice or Spreadsheet Competency Framework. Just click on the same Bookmark again to return to the default display. The Recent bookmark applies a filter to list the posts from the previous 2 months. There is also a separate ‘By date’ page that allows you to set a custom date range as a filter:

Next to our Bookmarks, there is a bar chart that represents articles that help support each module in the new range of on-demand Excel courses that are based on the Spreadsheet Competency Framework and use practice workbooks to help you assess your own level of expertise and to progress through the levels to achieve the Creator level. For those who have already purchased one or more modules, clicking on the appropriate module bar will filter the post list to show articles that will help with further exploration of the areas covered. Clicking on the graphic title will take you to the members’ special offer page if you haven’t yet explored what is available:

You can explore the ICAEW archive in more detail using this link: icaew.com/technical/technology/excel-community/archive or the embedded portal page below: