Excel Top Tip #6 - Build your professional toolkit

Here is the email sent to all subscribers on 24 September 2020. To receive Excel top tips just like this, you can subscribe by clicking here.

Hello

Following last month’s advice on how to hold an effective team Q&A session to develop internal Excel Masters, we’re looking at other ways you can build on your professional toolkit. 

If you work with data from online sources, PDFs, CSV, TSV, email or encrypted files, perhaps you’ll have found yourself asking team members for advice in how to manipulate and extract data from them…  

Can you show me how to…? Do you know how to…? What’s the quickest way to…? Do you have capacity to…?

It pays to have the skills to solve all these queries, with bonus points for using simple yet under appreciated tricks that are available within the tools you already have installed, just like these: Advanced Google Search can be extremely useful, as proved recently when we found the first “MT01” form submitted to Companies House thanks to this query:

site:beta.companieshouse.gov.uk "Commencement of a Moratorium" "2020" -VAM1

Spot the “”, - and site: to force, negate, and search a specific site, respectively.

Also, use the ‘Tools’ dropdown box next to the Google Search bar to filter search results by date, a useful trick for targeted investigations. Regex - Have you ever needed to remove all line-breaks from some text, or to replace all tabs with commas; working with data pasted from PDFs or other semi-structured sources often present this issue, and we’ve all done it manually once or twice - but regex is the tool you need to do this instantly.

Regex (a.k.a. Regular Expressions) is the term used for a structure of a search query that identifies types of characters. For example:

[0-9]{1,2} matches 1 or 2 numbers  \n matches a new line \t matches a tab

Best of all it’s built into Word and many other applications. Google “Regex and Word” to learn more about the options available, including how to insert these codes into ‘Find and replace’ to save all that manual effort. Excel keyboard shortcuts are most definitely underused.  The most common tricks are:

Select a row with Shift + Space Select a column with Ctrl + Space New row/column with Ctrl + Shift + = Remove row/column with Ctrl + - Edit a cell with F2 Word “invisible characters” hold the answer to 99% of all formatting questions in Word - they bring to light all those unexpected page breaks and inconsistent alignment issues; an essential component of using templates and sharing documents within a team.  Find the toggle button on the "Home" ribbon. QR codes have recently become front and centre as one of the ways we can navigate bars/restaurants and COVID testing centres. A really simple application of this technology comes when combined with a Google Form, as you can quickly collect data into a Google Sheet without sharing others’ data. A QR Code doesn’t just have to store a URL, it can store text, or contact details, and even trigger someone’s phone to make a phone call. These ready-to-go and easy to apply automation tips change repetitive tasks that are a drain on your team, into standardised processes which achieve efficiencies across the board. Understanding and applying the above will transform the way you’re seen amongst your peers, as you become a data master and the trusty source of knowledge in your team. 

You’ve done the hard bit; now apply these simple processes, with utmost attention to detail, to grab low-hanging fruit and increase your profile within your organisation. 

Thank you,

The StatementReader Team