The Biggest Pop Culture Moments of 2023
News
When you think of digital PR you may not think of spreadsheets, however, they are the bread and butter of any campaign.
Whether you’re using spreadsheets as part of a data-led digital PR campaign or simply as a way to track performance, knowing your way around them can really level-up your output.
Not every PR is a data whiz, this is where formulae can come in handy to automate and speed up the process. That’s why we have created the ultimate guide to Google sheet formulas to help maximise the stories that lie within your spreadsheets.
Ultimate Guide to Google Sheets Formulas for Digital PR’s
Formulas for Finance
From currency conversion to the stock market, Google Sheets has a whole range of formulas for you to explore.
The possibilities are endless when it comes to converting currencies. If your campaign isn’t landing in one country, why not try converting to another currency and re-pitching? You might be able to tap into a whole new audience.
1. Currency conversion: =A2*GOOGLEFINANCE(“CURRENCY:USDGBP”)
A great trick when working with money-related data, Google Finance’s currency conversion automatically fetches the most up-to-date conversion rates.
2. Companies current stock price: =GOOGLEFINANCE(“Ticker”, “price”)
Working with stocks? This trick allows you to fetch up-to-date stock prices for any company on the stock market.
3. Companies highest stock price in last year: =GOOGLEFINANCE(“Ticker”, “high52”)
If you want to know a company’s highest stock price over the course of the last year, use “high52”.
4. Companies lowest stock price in last year: =GOOGLEFINANCE(“Ticker”, “low52”)
If you want to know a company’s lowest stock price over the course of the last year, use “high52”
Formulas for text formatting
There’s nothing worse than copying and pasting some text into your spreadsheet to find that it’s all in capital letters. With these formulas you can automatically change formatting with a press of a button.
5. Anchor text: =$A$1
Anchoring cells allow you to drag across formulas without altering the cells used for the formula.
6. Capitalise first letter: =proper(A1)
If you’re working with names or places, or have simply been zooming through your data you might have forgotten a capital letter – this trick can automatically capitalise the first letter.
7. Google translate: =GOOGLETRANSLATE(A2, “en”,”es”)
A helpful tool when creating a global campaign, allowing mass translations to happen at the click of a button
8. All uppercase letters: =UPPER(A1)
This formula allows you to change all text in selected cells to uppercase.
9. All lowercase letters: =LOWER(A1)
This formula allows you to change all text in selected cells to lowercase.
10. Valid email checker: =ISEMAIL(value)
This trick is perfect for building media lists to prevent outreaching to dead emails
11. Convert number to text: =TEXT(number, format)
This formula will automatically convert any numeric values to written text.
12. Date: =DATE(year, month, day)
Using this formula, Google Sheets will automatically format the dates you’re working with so all dates are fluent.
13. Date if: =DATEDIF(start_date, end_date, unit)
If you need to find the number of days, weeks or months between two dates – this handy formula will work it out for you.
14. Date value: =DATEVALUE(date_string)
This formula changes a date string in a known format into Sheet’s accepted date format, this is great if you’re working with Americanised dates.
15. Current date/time: =NOW()
If you need to insert today’s date and/or time into your sheet – this formula will do it automatically without a calendar.
16. Detect language: =DETECTLANGUAGE(text_or_range)
If you’re working with other languages, this formula will detect which language is in a selected cell, allowing you to translate it with ease.
17. Valid URL checker: =ISURL(value)
Perfect for when adding hyperlinks or creating source lists, this formula checks if the link you’ve added is valid.
18. Count Characters in a cell: =LEN(cell)
Brushing up on your social media skills? This formula is perfect for planning out tweets so you can stick to the character limit.
Formulas for common calculations
You don’t have to be a maths expert to be able to use these formulas to generate new angles for your campaigns. Whether you’re creating a ranking index table or trying to find the average; put your phone calculator down and harness the power of Google Sheets.
19. Count: =COUNT(value1, [value2, …])
If you want to see how many cells are in your worksheet, or in a selected range – this formula will count them for you!
20. Count if: =COUNTIF(range, criterion)
If you want to count values in a selected range of cells based on specific criteria, this formula will do it automatically.
21. Percentage increase or decrease: =(New value – Old value) / Old value
Looking for a percentage? This formula allows you to find the percentage increase or decrease of data in your spreadsheet.
22. Percentage finder: =Part/Total
If you need to find the percentage of a value, this formula does it automatically for you.
23. Mean average: =average(A1:A10)
This trick fetches the average for a selected range of cells.
24. Median average: =median(A1:A10)
This formula automatically fetches the median average for a selected range of cells.
25. Vlookup: =VLOOKUP(A2,A:M,7,”FALSE”)
This famous formula looks up data from a table organized vertically, returning either exact or partial matches.
26. Hlookup: =HLOOKUP(search_key, range, index, [is_sorted])
Similar to a =VLookup, a =HLookup instead locates data from a table that is organised horizontally.
27. Correlation: =CORREL(data_y, data_x)
Great for when you’re working with statistics, =CORREL calculates the Pearson correlation coefficient between two sets of variable data and tells you whether there is a negative or positive correlation.
28. Net working days: =NETWORKDAYS(start_date, end_date, [holidays])
This formula is handy for working out your remaining holidays – =NETWORKDAYS returns how many whole working days between the specified start and end dates, excluding weekends and identified holidays.
29. Find unique data: =UNIQUE(range)
If you’re working with, or cleaning data and need to remove any duplicates – Google’s ‘=Unique’ formula pulls any unique data from a selected range.
30. Count the number of unique values: =COUNTUNIQUE(value1, [value2, …])
If you need to know how many unique values you have in your dataset, this formula will do that for you automatically.
31. Divide: =value1/value2
This formula allows you to divide values.
32. Sum: =sum(value1:value50)
This formula lets you add up all the values in the selected cells.
33. Max value: =MAX(value1, [value2, …])
This formula will find the maximum value in a selected range.
34. Min value: =MIN(value1, [value2, …])
This formula will find the minimum value in a selected range.
35. Mode: =MODE(value1, [value2, …])
The =MODE formula will fetch the most commonly occurring value within a selected range.
36. Rank: =RANK(value, data,0/1)
This formula ranks data from ascending or descending from a selected range of data.
37. Rank Average: =RANK.AVG(value, data, [is_ascending])
If the data you’re trying to rank contains duplicates, by using =RANK.AVG instead of =RANK will create an average for those duplicated cells.
38. Fixed number of decimal places: =FIXED(number, [number_of_places], [suppress_separator])
If your decimal points are all over the place, this formula will automatically round them to the specified number of decimal places.
39. Import data from URL: =IMPORTDATA(url)
=IMPORTDATA imports data from a given URL into .CSV or .TSV formats – this is great if you’re working with large data sets
40. Import data from table or list within a HTML page: =IMPORTHTML(url, query, index)
=IMPORTHTML automatically imports data into your spreadsheet from either a table or list on a HTML page.
41. Import range of cells from another spreadsheet: =IMPORTRANGE(spreadsheet_url, range_string)
This formula allows you to import a range of data from one spreadsheet to another.
42. If error: =IFERROR(original_formula, value_if_error)
If error messages such as #DIV/0 are overruling up your sheet, the =IFERROR function allows you to return either a different message or empty cell rather than the dreaded #DIV/0 sign
43. Array: =ARRAYFORMULA(array_formula)
=ARRAYFORMULA outputs a range of cells, whereas a regular formula such as ‘=sum’ would only output a single value.
44. If a condition is true or false: =IF(condition, “YES”, “NO”)
This formula performs a test on a value in a specific cell, providing different results – such as ‘Yes’ or ‘No, based on whether the data is true or false.
45. Search Function: =IF(SEARCH(“search phrase”,cell), “YES”)
Looking for a cell that says something specific, this formula will fetch it for you automatically – rather than spending ages sifting through your dataset.
46. Create Single-cell chart: =SPARKLINE(A1:A50,{“charttype”,”column”})
If looking at rows and rows of data is hurting your eyes, this formula whips up a quick single-cell sparkline chart to make visualising data a bit easier.
Formulas for formatting
Quite possibly some of my most-used formulas are based on formatting. From splitting text to columns, to making rows of text into columns (transpose). You’ll save yourself bags of time and speed up your output using these formulas.
47. Split text to columns: =SPLIT(B3,” “)
Great for if you’re working with first and last names in the same cell, =SPLIT will split clearly defined data into separate cells.
48. Transpose: =TRANSPOSE(array_or_range)
Is your imported data the wrong way round? =TRANSPOSE can instantly transpose (switch rows, to columns and vice versa) the selected range of data.
49. Merge cells: =CONCATENATE(range)
This formula will automatically merge any cells in the selected range.
50. Sort cells: =SORT(range, sort_column, is_ascending)
The =SORT function allows you to sort rows of a specified range of cells, in either ascending or descending order.
Further resources
Looking for free data sources? We’ve collated 100 free data sources where you can download and try out some of these formulas in real-time!
On the lookout for new data sources? Here are some of the most compelling data sources you can use in your next digital PR campaign.
How to find compelling data for your next digital PR campaign
Want to know what a data journalist thinks about your data? Ashley Kirk, visual projects editor at the Guardian revealed all in our webinar from earlier in the year. Watch again for free, here!
