Excel Functions for Data Analytics

Pivot Table Filters Rows Columns Values

1. Filters Purpose: To include or exclude data in the entire pivot table. When to Use: You want to view a subset of the data, but don't want to permanently remove any records. Example: Filter the pivot to show only data from 2023, or only for Region = East. Effect: Filters are applied before the pivot calculates totals/aggregates. 2. Rows Purpose: To group data vertically. When to Use: You want to categorize data into rows for comparison. Example: Put Product Name in Rows to list each product as a row. Effect: Creates row headers and defines how data is grouped. 3. Columns Purpose: To group data horizontally. When to Use: You want to break down data by a category across the top of the table. Example: Put Quarter or Region or Gender in Columns to compare across them. Effect: Creates column headers and allows side-by-side comparisons. 4. Values Purpose: To perform calculations or summaries (like sum, average, count). When to Use: You want to analyze the data numerically. Example: Put Sales or Quantity in Values to sum them for each group. Effect: Displays the numeric results in the body of the pivot. When looking at revenues, you can use control 1, go to the number tab, and use the 1000 separator (comma separator)

Average Function

=Average(A1:A5) Gives the average of cells: A1, A2, A3, A4, and A5 =Average(A1:B2) Gives the average of cells: A1, A2, B1, and B2

Count Function

=Count(A1:A5) Would tell you that there are five values, assuming no empty cells, text, logical values, or errors =Count(A1:B2) Would tell you there are four values, assuming no empty values etc.

Filter Function

=Filter(B5:C14,C5:C14>C2) This function only copies down a country's sales into the below columns if they generated more than 500,000 in USD

Nested IF Function

=IF(AND(C3>20,D3>5),"Yes","No") If both conditions are met, then yes, if not, then no Here, if they attract more than 20 clients, and are rated above a 5, then they do get a bonus, otherwise, they don't

IF Function

=IF(C3>20, "Yes", "No") This means if the value is greater than 20, it'll be labeled "Yes", otherwise, it'll be labeled as "No" Here, in the home tab: Conditional formatting was used Highlight cell rules Equal to: Yes Green fill with dark green text

IF Function Raise Percentage

=IF(D7>G$7,D7*G$10, "")

IFERROR

=IFERROR(A1/B1,"N/A") Returns a specified value (N/A) if a function or formula returns an error.

IFS Function

=IFS(C3>40,4000, C3>20,1000, C3<=20,0)

Min/Max Function

=Min(A1:A5) Gives the minimum from cells: A1, A2, A3, A4, and A5 =Min(A1:B2) Gives the minimum from cells A1, A2, B1, and B2 =Max(A1:A5) or =Max(A1:B2) does the same thing, just for the maxima

Sum Function

=SUM(A1:A5) Gives the sum of cells: A1, A2, A3, A4, and A5 =SUM(A1:B2) Gives the sum of cells: A1, A2, B1, and B2

SUMIFS Monthly Revenue

=SUMIFS($F$11:$F$22, $B$11:$B$22, $B6, $C$11:$C$22, C5) B6 is the country (France) and is written as $B6 so that the column is fixed The other ones are written like $F$11 so that you can drag the cell and the data values won't move C5 isn't fixed because you want to move it across

SUMIFS Function (Finding Values for A Different Name)

=SUMIFS(D3:D18,C3:C18,G3,B3:B18,"*"&G4&"*") The "*"& means that for a country like France, it looks for anything with "Spain" and tolerates there being anything in front of it The &"*" portion means the same, just with anything after it Both being put together means that it'll ignore any extraneous characters of any length as long as "Spain" is part of the cell

SUMIFS Function (For Two Criteria) Also COUNTIFS and AVERAGEIFS

=SUMIFS(D3:D18,C3:C18,G3,B3:B18,G4) The formula is used to look for the sum of olive sales in spain D3:D18 is meant to select the quantities for analysis C3:C18 is meant to look at the product types G3 is the specific product you're looking for (Olives) B3:B18 is meant to look at the different countries G4 is the specific country you're looking for (Spain) AVERAGEIFS works exactly the same way COUNTIFS just removes the first argument (D3:D18)

Day of the Week Excel Function

=TEXT(C2,"ddd") Gives 3 letter date abbreviation (Fri, Thu, Wed, etc.) 4 d's instead of three would give you the full name (Friday, Thursday, Wednesday, etc.) can do mmm for the three letter abbreviation for months

Textsplit Excel

=TEXTSPLIT(C6, "_") Splits the cell into two cells, with one cell being everything before the underscore, and the other after the underscore Different way to do the same thing is to highlight the cells, go to "data", then, "text to columns", then delimited, with the "other" delimiter being selected in this case, and being put in as an underscore (in the event that they're separated by spaces, you can just select that instead). Then move the destination to E6

XLOOKUP Monthly Revenue (With an &)

=XLOOKUP($B7&C5, $B$11:$B$22&$C$11:$C$22, $F$11:$F$22) B7 is the country (France) and is written as $B7 so that the column is fixed The other ones are written like $F$11 so that you can drag the cell and the data values won't move C5 isn't fixed because you want to move it across Functions the same as the SUMIFS function for monthly revenue

XLOOKUP Function (Trying to get two values)

=XLOOKUP(H3, B3:B20, E3:F20) This gives you the sales amount, and the commission for the worker in one function, instead of two

Nested XLOOKUP Function (Two XLOOKUP functions combined)

=XLOOKUP(H3, B4:B20, XLOOKUP(I3, C3:F3, C4:F20)) Cell H3 is the salesperson Cell I3 is the year This finds the commission for Harley (whose name is in B5) in the year 2020

XLOOKUP Function (Not on the List)

=XLOOKUP(H8, F3:F20, E3:E20, "Not on the List") =XLOOKUP(Cell# you want to find the value for, column where the thing is that you want to find the value for (name), column name where the actual value is in, "what should be typed in if the value isn't on the list) Output: Not on the List If the name requested IS on the list, then it'll just put the actual value corresponding to the name

XLOOKUP last to first

=Xlookup(D16,D2:D12,E2:F12,,0,-1) Reference cell Column with the employee values Column with the return array (both values you want, those being task and status) Nothing if not found 0 for exact match -1 for search mode which is last to first

Concatenate Function Put a name in one cell Put a phone number in one cell Put a date (Month, Day, Year format)

=concat(A1, A2, A3) or (A1, B1, C1) If you want to do a name like John Doe while spacing the first and last names =concat(A1," ",A2) or if you want to hyphenate it (A1,"-",A2) or if you want to do last name first name (A2," ",A1) If you want to do a phone number like 894-094-6789 =concat(A1, "-", A2, "-",A3) In that instance, in the excel cell, 094 would ordinarily be inserted as 94, dropping the leading 0 To solve this, see "How to keep a leading zero" If you want to do a date, like January 21, 2015 =concat(A1, " ", A2, ", ", A3)

datedif function (number of years months and days since one date)

=datedif($F4, $B$1, "Y") for years =datedif($F4, $B$1, "YM") for months =datedif($F4, $B$1, "YD") for days

How old someone is in years (DATEDIF function)

=datedif(E8, Today(), "Y") Remove decimals (the one with the right arrow in the home section (Start date, end date, years) Could be months, or days If you're using a date besides today, in lieu of today(), use the cell number with a $ sign ($C$7) If you're doing three columns, one for days since, one for months since, and one for years since, use $E8

Find someone's revenue

=index(D6:D16,MATCH(F10,B6:B16,0)) The 0 here refers to there being an exact match The match portion is about finding Sarah's name from the B column, which will help match revenue XLOOKUP is also viable (See that card)

Finding 2nd 3rd 4th largest

=large(B2:B30,2) =large(B2:B30,3) =large(B2:B30,4) Here, it's useful to have a column with rankings (so 1-10), and have the function be: =sequence(10) In the column on the right: =large(B2:B30,C1:C10) This would give you the 10 largest values from the B column

Business days of shipping

=networkdays(today(),A1) business days from today until the date on A1

Round Up Function

=roundup(A1,0) Rounds up to the next whole number, (A1,1) would be to the next tenth place

Convert Months to Quarters

=roundup(Month(C7)/3,0) Month/3 for quarters 0 for no decimals From there, you can click control 1, and in custom formatting, change from 0, to Q0 This will make quarters Q1, Q2, Q3, and Q4

Number Rows in Sequence

=sequence(50) For 50 rows =sequence(50,,DATE(2023,01,01),7) For making dates 50 rows of values ,, means the data isn't moved horizontally The date function gives the start date (01-01-2023) The 7 means the interval for dates is 7 (so once every 7 days, or once a week)

Monthly Calendar

=sequence(6,7,DATE(2024,01,01)) Rows, columns, date in YMD format

Amount of days after today

=today() function mentions what day it is today =A1-today() tells you the number of days from "today" for that date (this works for shipping)

Remove excess spaces, leaving only one

=trim(A1) This works if you want to turn a cell like: George Washington Into: George Washington

Current week of the year

=weeknum(A1,2) Weeknum of date A1, based on monday being the start of the week (1 would be for sunday)

Salary Calculations for Prorated Contract (For those who left halfway through the month)

=yearfrac(E5, F5)*D5 Start date, end date, multiplied by yearly salary

HLOOKUP Function Horizontal Lookup Data

A function that performs a horizontal lookup by searching for a value in the top row of the table and returns the value in the same column. Looks up a value in a horiztonal lookup table where the first row contains the values to compare with the lookup table =HLOOKUP(H3, A1:G5, 5, False) The cell range would likely be vastly different, but it's the same general format =HLOOKUP(Cell#, Table Range, Column number, Exact match)

Changing Dollars to Pounds

Command 1 Can add decimals from there Could work with percentages, or many other things

Copy paste a cell to the right, or down

Command R Command D

Replace a typo

Control H (on mac) If you want to replace Housston with Houston Just type Find What: Housston Replace With: Houston

Show all functions on the sheet

Control ` (for mac)

How to find the amount of something needed to make a certain profit

Data tab What-If Analysis Goal Seek Set the profit to your desired amount (could be a lot of other things) "By changing cell" should be the cell that you want to find the sales that you need, such as 2567 sales, to get to 10,000 profit In that case, the "____ sold" cell will be the one you want to change.

First Name Middle Name Last Name

First Name: =LEFT(Q10, FIND(" ", Q10) - 1) Middle Name: =TRIM(MID(Q10, FIND(" ", Q10) + 1, LEN(Q10) - LEN(LEFT(Q10, FIND(" ", Q10))) - LEN(TRIM(RIGHT(SUBSTITUTE(Q10, " ", REPT(" ", 100)), 100))))) Last Name: =TRIM(RIGHT(SUBSTITUTE(Q10, " ", REPT(" ", 100)), 100))

Pivot Tables Largest to Smallest Proportions Average Sales Price

For largest to smallest: Right click on one of the cells, find sort, and sort by largest to smallest For proportions: Right click on one of the cells, go to "show values as," and it'll give you all of the types of proportions you can take the values as (percentage of grand total, percentage of column total) For average sales price, take that type of column (price per unit), move it to values, and when it shows up as sum of price per unit, right click on one of the cells, summarize values by "average"

Replace Function

For the cell ExcrlHQ (Desiring ExcelHQ) =Replace(A1, 4, 1, "e") Replace(Cell#, character number, length of characters, "What to put in its place")

Substitute Function

For the cell ExcrlHQ (Desiring ExcelHQ) =Substitute(A1, "r", "e", 1) Substitute(Cell#, character to be substituted out, character to be subbed in, instance number (here being the first r in the cell) For the cell repel (Desiring repal) =Substitute(A1, "e", "a", 2)

Joining Two Cells (Alternative to Concat

For the cells London UK =(F3&", "&G3) Output: London, UK

SUMIFS Function (For one criterion)

For the months sales value =SUMIFS(F3:F14,E3:E14,I3) This looks for the sales in every month that matches the value in cell I3. In cell I3, the value was 7 (July), therefore, the function summed all of the sales from all companies in the month of July For the company name sales value =SUMIFS(F3:F14,B3:B14,I6&"*") What this does is it examines sales, based on company name (criteria), where the company name is the same as I6, plus any characters that follow the company name In the original cell, the name in cell I6 is "Amazon", but there are many divisions of Amazon in the dataset, such as Amazon UK, Amazon Inc., etc. I6&"*" counts the values corresponding to all of the Amazon values.

Index Match Function

For trying to find sales in a country in a given month =Index(C3:J11,MATCH(B14,B3:B11,0),MATCH(C14,C2:J2,0)) Here, the country was "Austria" and the month was "April" The C3:J11 index is to look at all of the sales numbers from all countries in all months The first MATCH function: MATCH(B14,B3:B11,0) Is to find the country Austria within the country column The second MATCH function: MATCH(C14,C2:J2,0) Is to find the month April within the month row In both cases, the 0 means that the request is for an exact match

Uppercase, lowercase, and proper formatting (very important for data cleaning)

Given the cell aBrAHAm o. LiNCOln =upper(A1) Converts it to: ABRAHAM O. LINCOLN =lower(A1) Converts it to: abraham o. lincoln =proper(A1) Converts it to: Abraham O. Lincoln Even in instances where a letter shouldn't be capitalized, like "the" in a book title, the proper function will capitalize it Additionally, proper will not fix spacing issues like aBrAHAm o. LiNCOln In this case, you'd do: =trim(proper(A1)) To convert it to: Abraham O. Lincoln

Substituting/replacing/removing improper characters

Given the cell: Abe[ Lincoln =substitute(A1, "[", "") This replaces "[" with nothing, essentially dropping it The aforementioned function converts it to: Abe Lincoln However, this is only if you sub the "[" with nothing, if you were to sub it with a space, then there would be two spaces between Abe and Lincoln. That's where the trim function would show up Given the cell aBrAHAm o. _ LiNCOln =TRIM(PROPER(SUBSTITUTE(A1, "_", " "))) The aforementioned function converts it to: Abraham O. Lincoln The presence of the trim function makes it meaningless that the underscore was replaced by a spacebar Given the cell aBrAHAm o. _: LiNCOln =TRIM(PROPER(SUBSTITUTE(SUBSTITUTE(A1, "_", " "), ":"," ")) The aforementioned function converts it to: Abraham O. Lincoln

Highlight the 10 lowest

Go to conditional formatting Top Bottom Rules Top 10 Lowest

Convert to day month year excel

Highlight all the data, go to the data tab, click on text to columns, click next twice, then select date format (may be in DMY or MDY), select a destination where you want the dates, and click on finish

Descriptive Statistics for Data Analysis

Highlight the column, go to the data tab, find data analysis on the right, one use case is descriptive statistics, then select the input range, select output range, and put the cell you want to start displaying the data in (such as $F$2)

Create a chart showing product and service revenue and gross margin percentage

Highlight the rows Since some are numbers, and the others percentages, make a combo chart (insert combo chart) Right click on the gross margin% in the legend, and go to format data series. Plot series on secondary axis To display dates, right click on the numbers on the x axis, click "select data", and select the years You can then remove gridlines, and change the colors of the bars and lines

Delete column/row

Hold: Command -

Add column/row

Hold: Shift Command +

Autofitting Row Height and Column Width

Home tab, format, select all, autofit row height and column width

Control E Excel

If you type in the day, month, and year of the first cell into subsequent columns, and hit control e (on mac), then it'll autocomplete the columns for you

XLOOKUP Function (Finding Values for A Different Name)

If you want to find sales amount for Nike, but in the excel document, it's listed as Nike Inc. =XLOOKUP(H3&"*", B3:B14,E3:E14,,2) Cell H3 contains: Nike H3&"*" here means to take what's in cell H3, and add an indeterminate number of characters to find the cell that contains Nike B3:B14 represents the column with the actual company names E3:E14 represents the column with the values that need to be found (sale amounts) ,, means there's nothing we're looking to put if the cell isn't found 2 means we want a wildcard character match, because the actual cell in the sheet is not Nike, but Nike Inc.

How to keep a leading zero

In this case, you can write 094 as '094, or insert the function =Text(A2, "000") Both force the cell to be 094 If you want, you can paste that cell as a value only, however, since it's the same as the '094, if it were to be summed with a value like 162, the sum of the two cells would be 162, not 256 If you want to solve the summation problem, it's easiest to just accept the fact that you'll have to give up the leading zero

$ Sign Excel

Indicates an absolute cell reference, meaning the cell reference will not change when copied or moved to another cell. This is useful when you want to refer to a specific cell, even if your formula's location changes G$7, not $G7

Left, Right, Middle Function

Returns the characters in a text string based on the number of characters you specify starting with the far-left character in the string. For the cell AL Montgomery 95402 =Left(D1,2) Gives you the state abbreviation: AL For the same cell =Mid(D1, 4, 10) Gives you the city Montgomery Here, 4 represents that the "M" in Montgomery is the 4th character in the cell, and it being 10 characters is what makes up the last part of the function For the same cell =Right(D1, 5) Gives you the Zip Code

Power Query Revenue Moving data to an excel sheet Pivot Table

Revenue: Price * Units Sold (P*Q) Highlight the two columns, go to add column, click on standard, then multiply To the left of every title, there's a couple numbers, you can click on that, and it gives you options of currency, decimal number, whole number, etc. Separating a Column into Two (Region-State): Highlight the column, go to transform, split column, split by delimiter, and type in the delimiter, and rename the two columns Going to home, and clicking Close and Load puts the data onto an excel sheet and takes you back to excel Pivot Table: Insert, Pivot Table, use as normal Handling a new spreadsheet: Go to PivotTable Analyze, click on refresh -> refresh all Power Query detected everything inside the folder, so a new entry into the folder can be easily handled.

Alternative way to Make a Filter (Top row)

Right click, go to filter, and click filter by selected cell's value

XLOOKUP Function (Basic)

Searches a range or an array and returns an item corresponding to the first match if finds. If a match does not exist, it can return the closest (approximate) match. =XLOOKUP(H8, F3:F20, E3:E20) =XLOOKUP(Cell# you want to find the value for, column where the thing is that you want to find the value for (name), column name where the actual value is in) Output: $1,239

Show results by Quarter Units Sold Total Sales Average Sales Price

Select all data Insert pivot table Place in existing worksheet By quarter, so date column dragged into the rows bin. Remove date, and quarter and year will remain Purchase (quantity sold, total sales, and sales price will be found here) should be dragged into the values bin From here, right click, and go to "value field settings" Count of purchase for units sold Drag again, value field setting for sum of purchase Repeat with purchase to find average by using the value field setting Change name by going to value field settings, and typing in a different name

Hiding Parentheticals

Select everything you want to clean, click on control H, go to find and replace, and click on replace. Then replace: (*) with nothing (leave the 2nd text box blank) and hit replace all

Filling empty cells

Select table, go to home, find and select, click go to special, select blanks, and click ok, after which, you'll be on one of the cells, type in N/A, and control enter

Remove Duplicates

Select table, go to the data tab, remove duplicates

Selecting a row or column

Shift space selects a row Control space selects a column

Move a row up/down

Shift space to select the row, hold the top of the cell, and hold shift while dragging it up or down

First Name Last Name

Type in the First name of the first person, then control e Do the same with last names

Freeze top row (So it always displays when you scroll down, avoiding confusion)

View, freeze top row, will easily be found on the menu

Pivot Tables Visualizing Dates (as a timeline) Adding a Slicer (By region) Charts

Visualizing Dates: Go under PivotTable Analyze Select "Insert Timeline" Select it for dates Once that happens, you'll have a timeline from January to December You can then select which months you want to analyze, or by which quarters, days, or years you want to analyze Slicer by Region: Instead of adding regions into the column bin, you can go to PivotTable Analyze, and add a slicer, here, by "Region" Charts: Go to PivotTable Analyze Add PivotChart From there, you can add something like region to the legend (series) bin

Convert year 24 to 2024

control 1 in custom formatting, change from dd/mm/yy to yyy, or yyyy could do dd-mmm-yyy for day, month abbreviation (sep, jan, etc), year (2024)

Go to a certain row

control G reference A100 for row 100

Shows you the values that have been in a column

option down (on mac)

Power Query Odd date given Merging Columns Converting Decimals to Whole Numbers (For Units sold) Trim Proper Alternative

Odd date given: On the home tab, go to date type, and click on date from the bar below Merging two columns: Select two columns, right click, select merge columns, separator (likely a space bar), and select a name for the column Converting Decimals to Whole Numbers (For Units sold): Highlight the data, go to transform, rounding, and click on round up (there are instances where you round down, but you'd round up for how many units you need to sell) Trim Proper Alternative: Highlight the data, go to transform, format, select "capitalize each word," and also select "trim"

Pivot Tables Visualization Scatter Plots Box and Whisker

Pivot Tables Visualization Select the values, on the home tab, go to conditional formatting You can select data bars Scatter plots: Select the columns for analysis Create a scatterplot Cut and paste to move it to the top, selecting a cell to paste it in For trendlines, right click, hit "display trendline," after which, you'll be able to display the trendline equation and r^2 Box and Whisker: Select a column The box and whisker plot will be found in the insert chart tab, under the one that looks like a histogram

Pivot Tables Profits from Revenue - Costs column Group by quarters Adding new data Basic Data Analysis on Pivot Tables

Profits: Go to PivotTable Analyze Fields, items, & sets Go to calculated field Name it Profit In the formula tab, type in: = Revenue - Expenses (The revenue and expenses columns can just be selected) Group by quarters: After dragging date (presumably in months) into the columns, or rows bin in the pivot table, right click on one of the months, and go to -> group, and select the one with quarters You can keep the months as well, and just right click on one of the months and collapse, or expand when the months aren't displayed Adding new data: Go to PivotTable Analyze Select refresh Basic Data Analysis on Pivot Tables: On the right of the home tab, there's an analyze data button, and it opens an AI generated data analysis tab where you can ask questions (such as revenue by salesperson). Additionally, it offers you charts and visualizations

EDATE Function

Requires two values, a date and the number of months you want to add or subtract For the cell: Jan-23 To make one for every subsequent month =EDATE(A1,1) A1 is the start date, 1 is the months Could be -1 if you wanted to go backwards

VLOOKUP Function Vertical Lookup Data

Looks up a value in a vertical lookup table and returns a related result from the lookup table A vertical lookup table is one where the columns are the main categories, rather than the rows =VLOOKUP(H3, A1:G5, 5, False) =VLOOKUP(Cell#, Table Range, Column number, Exact match) True would be an approximate match If you switch to a different person's name, then it'll give you the correct value for that

Filter by Slicers

Make a table Control T (on mac) Under the table bar, select insert slicer The slicer will be based on a certain category Then you can click on a certain value corresponding to a column Can multiselect as well

Power Query Merging Multiple Files Into One (From a folder) Delete columns & parts of text Subtracting days

Merging Multiple Files Into One (From a folder): Opening Power Queries Found Here After being given a folder with different spreadsheets for different retailers (or any other category) Open one of the files to view things, such as the person's name, and/or the corresponding store location (if it's in the same cell, you can remove it) Open a new excel file, go to data, click on get data, from file, from folder Combine all of the sheets you want from the folder into the excel sheet Combine and transform if you want to make changes to the data From there, on the next window that pops up Delete Columns & Parts of Text: You can delete columns, and if you want to remove parts of text that are the same across different spreadsheets, go to transform, extract text before delimiter if you want the first part of the text, and extract text after the delimiter for the opposite case Undo any editing by going to the applied steps bar on the right, clicking on the thing that has been removed undoes the previous step. Subtracting Days: In order to see days since (such as days from when a product is ordered to when it arrives), highlight both the necessary columns, go to add columns, go to date, and click on subtract days Double Clicking on the column name gives you the option to rename the column