5 Excel tips and tricks every royalties specialist should know
Microsoft Excel, the second language of every successful royalty manager. Taking Curve out of the equation, there is no better tool to assist you with your royalty accounting processes than Excel. Our commitment to transparency within the royalties sector combined with our desire to give our clients the very best service means we are always keen to share our secrets for success. Here are the top five tricks we believe every royalty manager should have up their sleeves.
Text To Columns
When laying your eyes upon your latest royalty statement, you may expect a complete set of data beautifully separated across rows and columns. That may, however, not always be the case. Data in a csv format may sometimes be displayed to you entirely in column A, separating the different columns of data with nothing more than a comma. Text To Columns can separate this data into individual columns for you.
To separate your data, select the first column of your file, then select the Text To Columns tool (ALT+A+E for whom every second counts) in Excel and select your delimiter. This is most often a comma, but may also be a semicolon or any other character that was used to delimit the data. When you complete the function, Excel will separate your data into different columns making it easy for you to read and understand.
Filters
Your royalty statement may (desirably) hold more royalty lines than you can possibly flick through. Filters allow you to only display those royalty lines that match certain conditions. Additionally, they can also be used to rapidly sort your data in a desired order.
To get started, highlight your header row and select the Filter tool (or tap ctrl+shift+L). Your filters will display on your header row, ready for you to query all the rows in the spreadsheet that match your criteria.
Pivot Tables
Just looking at the rows and columns in your spreadsheet is most often not the best way to spot bigger trends in your data. A Pivot Table is a powerful tool to calculate, summarise, and analyse data that lets you see comparisons, patterns, and trends in your data.
First, highlight the data in your spreadsheet. Then, go to the Insert tab, select Pivot Table and hit Ok. Your Pivot Table is now ready for you to customise. Which filters, rows, columns and values to select is entirely dependent on what you are aiming to achieve.
In the below example; we use filters to only display our Streaming data. We use Rows to tell Excel we want our data broken down per Source. And we use Values to tell Excel we want to know what is the sum of the total Net Amount per Source. Furthermore, we can sort the data in our pivot table.
VLookup
The single most vital Excel formula of any royalty manager who regularly handles large sets of metadata in Excel. This formula allows you to instantly display a connected piece of data to your given value. It is a neat little trick that comes in handy in many scenarios.
The Vlookup formula is constructed as per below.
=VLOOKUP (value, range, return column, exact)
In our example, we are given a list of ISRC codes. We wish to find out the matching Track Title of each of these ISRC codes by looking up this data in our royalty statement.
Value - What is the value that you want to look up? In our example, we want to look up the ISRC code; we select the cell containing the ISRC code for which we’d like to find the matching Track Title.
Range - Where do you want to look for this value? We want to look for this ISRC in the royalty statement, so select this as our range. It is important that we select the ISRC column as the first column of our range, since this is the column where we want to look for our ISRC value. And it is important that our range includes the Track Title column, since we will want to tell Excel later to return the value from this column.
Return Column - What is the column number in the range of the value that we wish to return? The Track Titles are specified in the 2nd column of our range, so we set the return column to 2.
Exact - Do we want Excel to return a result only when there is an exact match with our Value (=FALSE), or are we happy for Excel to return a result when there is an approximate match (=TRUE)? In our example, we only want to return Track Titles when Excel finds an exact match for our ISRC code, so we set this to FALSE.
Our Vlookup instantly returns the Track Title of our given ISRC. We can drag down our formula for Excel to return the Track Titles of all the ISRC codes in our column.
Remove Duplicates
Looking to get a list of unique values in your column? The remove duplicates tool does what it says on the tin. Simply highlight your column, go to the Data tab, and select the Remove Duplicates tool. All duplicates will be removed, and you will be left with a list of the unique values in your column.
