{"id":165,"date":"2023-08-30T20:40:59","date_gmt":"2023-08-30T20:40:59","guid":{"rendered":"https:\/\/editor.mediahack.co.za\/databites\/?p=165"},"modified":"2025-11-17T17:41:35","modified_gmt":"2025-11-17T17:41:35","slug":"use-these-ten-super-simple-formulas-to-improve-your-spreadsheet-game","status":"publish","type":"post","link":"https:\/\/outliereditor.co.za\/index.php\/2023\/08\/30\/use-these-ten-super-simple-formulas-to-improve-your-spreadsheet-game\/","title":{"rendered":"10 spreadsheet formulas we use to superpower our data analysis"},"content":{"rendered":"\n<p id=\"538a\">Both Microsoft Excel and Google Sheets are powerful spreadsheet programmes for storing and sorting through mounds of data. Once you learn how to make even the most basic calculations using the tools, the possibilities for data stories are endless.<\/p>\n\n\n\n<p id=\"15e3\">Here are 10 of our favourite\/most-used spreadsheet formulas for making sense of data. Note: these functions can be used in both Excel and Google Sheets.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">SUM<\/h2>\n\n\n\n<p>This is one of the most basic functions but it\u2019s also one of the most popular. It can be used to add two or more numeric values.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/editor.mediahack.co.za\/databites\/wp-content\/uploads\/sites\/3\/2023\/08\/SUM.webp\" alt=\"\" class=\"wp-image-166\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">AVERAGE<\/h2>\n\n\n\n<p>This function automatically calculates the average of a range of numbers. This is done by adding up all the numeric values and dividing that total by the number of values in your sum.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/editor.mediahack.co.za\/databites\/wp-content\/uploads\/sites\/3\/2023\/08\/AVG.webp\" alt=\"\" class=\"wp-image-167\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">MODE<\/h2>\n\n\n\n<p>This function will return the most commonly used value in a dataset.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/editor.mediahack.co.za\/databites\/wp-content\/uploads\/sites\/3\/2023\/08\/MODE.webp\" alt=\"\" class=\"wp-image-168\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">IF<\/h2>\n\n\n\n<p>The IF function allows users to set a condition that can end in one of two results: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If a certain cell meets the condition the user set, it will be true. For example:\u00a0If<em> <\/em>the percentage is higher than 30% then it is a pass<\/li>\n\n\n\n<li>If a certain cell does not meet the condition the user set, it is false. For example:\u00a0If the percentage is lower than 30% then it is a fail<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/editor.mediahack.co.za\/databites\/wp-content\/uploads\/sites\/3\/2023\/08\/IF.webp\" alt=\"\" class=\"wp-image-169\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">MAX\/MIN<\/h2>\n\n\n\n<p>These two functions do exactly what it sounds like they do: they return the highest and lowest values in a range. The MAX function can be used to find the highest value and the MIN function can be used to find the lowest value.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/editor.mediahack.co.za\/databites\/wp-content\/uploads\/sites\/3\/2023\/08\/MAXMIN.webp\" alt=\"\" class=\"wp-image-171\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"832f\">COUNTIF<\/h2>\n\n\n\n<p id=\"832f\">COUNTIF will return the number of cells that meet certain criteria.<\/p>\n\n\n\n<p id=\"56db\">In the example below, to find out how many of the employees come from South Africa:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Type in your function. <\/li>\n\n\n\n<li>Select the range of countries (B2:B12)<\/li>\n\n\n\n<li>Type in what you want Google Sheets to look for. In this instance, it&#8217;s &#8216;South Africa&#8217;. <\/li>\n<\/ul>\n\n\n\n<p>The answer is 5 employees are South African.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/editor.mediahack.co.za\/databites\/wp-content\/uploads\/sites\/3\/2023\/08\/COUNTIF.webp\" alt=\"\" class=\"wp-image-170\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"0ccb\">SUMIF<\/h2>\n\n\n\n<p id=\"0ccb\">This function adds values together based on a certain criterion. In the example, we want to add up the total salary amount of the South African employees only.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/editor.mediahack.co.za\/databites\/wp-content\/uploads\/sites\/3\/2023\/08\/SUMIF.webp\" alt=\"\" class=\"wp-image-172\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"d1db\">SPLIT<\/h2>\n\n\n\n<p id=\"d1db\">The SPLIT function allows a user to break up data by separating it wherever a specific &#8216;separator&#8217; occurs. Separators can be periods, commas, semicolons or spaces. In the example below, commas are used to divide the data, so that is what is indicated in the formula &#8221; , &#8220;<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/editor.mediahack.co.za\/databites\/wp-content\/uploads\/sites\/3\/2023\/08\/SPLIT.webp\" alt=\"\" class=\"wp-image-173\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"bf4e\">COUNT\/COUNTA<\/h2>\n\n\n\n<p id=\"bf4e\">COUNT and COUNTA do precisely what it sounds like they do. These functions count the number of cells with either text (COUNTA) or numbers and dates (COUNT). This means both functions exclude any blank cells.<\/p>\n\n\n\n<p id=\"e4db\">These functions are helpful when you know how many rows of text or numbers you should have in a document filled with random blank cells. You can use these two functions to check yourself.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/editor.mediahack.co.za\/databites\/wp-content\/uploads\/sites\/3\/2023\/08\/COUNTA.webp\" alt=\"\" class=\"wp-image-174\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"0b27\">TRIM<\/h2>\n\n\n\n<p id=\"0b27\">Sometimes it\u2019s difficult to tell if there are multiple spaces between the words in different cells. The TRIM function works by leaving only a single space between words, making your information appear much neater.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/editor.mediahack.co.za\/databites\/wp-content\/uploads\/sites\/3\/2023\/08\/TRIM.webp\" alt=\"\" class=\"wp-image-175\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Notebook<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Follow our\u00a0<a href=\"https:\/\/www.tiktok.com\/@mediahackza?is_from_webapp=1&amp;sender_device=pc\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>TikTok micro-training journey<\/strong><\/a>\u00a0where we show you the tips and tricks we use when working with Google Sheets.<\/li>\n\n\n\n<li>Learn with The Outlier Learning.\u00a0Find out more about our training courses <a href=\"https:\/\/www.theoutlier.co.za\/learning\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a><\/li>\n\n\n\n<li>&nbsp;<a target=\"_blank\" href=\"https:\/\/newsletters.theoutlier.co.za\/\" rel=\"noreferrer noopener\">Subscribe to The Outlier<\/a>, a fortnightly newsletter that delivers data-driven insights<\/li>\n\n\n\n<li>\u00a0<a href=\"https:\/\/newsletters.theoutlier.co.za\/\" target=\"_blank\" rel=\"noreferrer noopener\">Sign up to the DataBites newsletter<\/a>\u00a0for more on\u00a0data storytelling, tips and stories<\/li>\n<\/ul>\n\n\n\n<p id=\"a613\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Both Microsoft Excel and Google Sheets are powerful spreadsheet programmes for storing and sorting through mounds of data. Here are our favourite formulas for making sense of data.<\/p>\n","protected":false},"author":3,"featured_media":86688,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[447,448,1387],"tags":[473,474,475,476,454,477,478,479],"newsletter-post":[],"site":[],"class_list":["post-165","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databites","category-how-to","category-the-outlier","tag-cleaning-data","tag-data-wrangling","tag-excel","tag-formula","tag-google-sheets","tag-messy-data","tag-spreadsheets","tag-tips"],"acf":{"big_number":null,"big_number_caption":null,"big_number_link":null,"big_number_background":null,"big_number_text_colour":null,"big_number_icon":null,"big_number_wide":null,"featured_chart":null,"flourish_chart_id":null,"flourish_sub_title":null,"flourish_chart_width":null,"is_newsletter_post":null,"post_style":null,"show_on_front":null,"link_through":null,"chart_url":null,"background_colour":null,"text_colour":null},"_links":{"self":[{"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/posts\/165","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=165"}],"version-history":[{"count":1,"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/posts\/165\/revisions"}],"predecessor-version":[{"id":86689,"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/posts\/165\/revisions\/86689"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/media\/86688"}],"wp:attachment":[{"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=165"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=165"},{"taxonomy":"newsletter-post","embeddable":true,"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/newsletter-post?post=165"},{"taxonomy":"site","embeddable":true,"href":"https:\/\/outliereditor.co.za\/index.php\/wp-json\/wp\/v2\/site?post=165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}