# PracticeExcel > Interactive Excel and Google Sheets practice in the browser. Every exercise is a live spreadsheet: you type the formula, and it is graded the way Excel calculates it, with a hint when it is wrong. Also function references, keyboard shortcut drills, timed mock Excel tests for job applications, and certificates. - Language: American English, with English function names (VLOOKUP, SUMIFS) and commas between arguments. - Free without an account: Getting started, the keyboard shortcuts, the whole SUM, AVERAGE and COUNT topics, the first lesson in every other topic, and a timed preview of every mock test. Premium unlocks the rest ([Pricing](https://practiceexcelonline.com/pricing.md)). - Danish version: [ExcelOpgaver](https://excelopgaver.dk/llms.txt), with Danish function names (LOPSLAG, SUM.HVIS), semicolons between arguments, and a decimal comma. - Each link below goes to the page as Markdown; leave out `.md` for the interactive page. ## Excel practice exercises - [Getting started: The grid](https://practiceexcelonline.com/practice/the-grid.md): 4 lessons - [Welcome](https://practiceexcelonline.com/practice/the-grid/welcome.md): Get started: see how a lesson works and make your first click in the spreadsheet. - [Cells, rows and columns](https://practiceexcelonline.com/practice/the-grid/cells-rows-and-columns.md): Find your way around the grid. Click cells by their address and type in one. - [Select a range](https://practiceexcelonline.com/practice/the-grid/select-a-range.md): Select ranges of cells with the mouse, and learn how a range is written. - [Type text and numbers](https://practiceexcelonline.com/practice/the-grid/type-text-and-numbers.md): Type a heading, a number and a correction into the sheet. - [Getting started: First formulas](https://practiceexcelonline.com/practice/first-formulas.md): 4 lessons - [Add two cells](https://practiceexcelonline.com/practice/first-formulas/add-two-cells.md): In B4, write a formula that adds the price of the coffee and the cake. - [Multiply quantity by price](https://practiceexcelonline.com/practice/first-formulas/multiply-two-cells.md): In D2, multiply the number of units by the price. - [Click cells into a formula](https://practiceexcelonline.com/practice/first-formulas/click-cells-into-a-formula.md): In B5, add up the visitors for the three weeks by clicking the cells into your formula. - [Parentheses change the order](https://practiceexcelonline.com/practice/first-formulas/order-of-operations-parentheses.md): In E2, work out the total: the units plus the bonus units, all times the price. Put the addition in parentheses so it happens first, (B2+C2)*D2. - [The basics: SUM](https://practiceexcelonline.com/practice/sum.md): 4 lessons - [Add up a column with SUM](https://practiceexcelonline.com/practice/sum/sum-a-column.md): In B6, use SUM to total the Units in B2:B5. - [SUM a block of cells](https://practiceexcelonline.com/practice/sum/sum-a-block-of-cells.md): In F2, total the sales of every region in every quarter with one SUM over B2:D4. - [SUM two separate ranges](https://practiceexcelonline.com/practice/sum/sum-separate-ranges.md): In G2, add up Rent and Fun for both months. They are not next to each other, so give SUM two ranges separated by a comma. - [SUM skips text that + can't](https://practiceexcelonline.com/practice/sum/sum-ignores-text.md): In B7, add up the sales in B2:B6. Aalborg hasn't reported yet and says n/a, so use SUM, which skips text, rather than adding the cells with +. - [The basics: AVERAGE](https://practiceexcelonline.com/practice/average.md): 3 lessons - [Find the average with AVERAGE](https://practiceexcelonline.com/practice/average/average-function.md): In B7, use AVERAGE to find the mean score in B2:B6. - [AVERAGE skips blank cells](https://practiceexcelonline.com/practice/average/average-blank-cells.md): Tuesday has no sales figure yet. In B7, use AVERAGE on B2:B6 to get the average of the days that do have one. - [Round an average with ROUND](https://practiceexcelonline.com/practice/average/round-an-average.md): In E1, work out the average number of visitors per day, rounded to one decimal. Put AVERAGE inside ROUND: ROUND(AVERAGE(B2:B7),1). - [The basics: COUNT, COUNTA and COUNTBLANK](https://practiceexcelonline.com/practice/count.md): 3 lessons - [Count numbers with COUNT](https://practiceexcelonline.com/practice/count/count-numbers.md): Some orders have a note instead of a number. In B8, use COUNT to count how many orders in B2:B7 have a number of units. - [Count filled-in cells with COUNTA](https://practiceexcelonline.com/practice/count/counta-non-empty-cells.md): In E2, use COUNTA to count how many people in B2:B7 have given an email address. - [Count empty cells with COUNTBLANK](https://practiceexcelonline.com/practice/count/countblank-empty-cells.md): In E2, use COUNTBLANK to count how many phone numbers are missing in B2:B7. - [The basics: MIN, MAX and LARGE](https://practiceexcelonline.com/practice/minmax.md): 4 lessons - [Find the fastest time with MIN](https://practiceexcelonline.com/practice/minmax/min-function.md): In B8, use MIN to find the fastest (lowest) 100 m time in B2:B7. - [Find the best month with MAX](https://practiceexcelonline.com/practice/minmax/max-function.md): In E2, use MAX to find the highest sales figure in B2:B6. - [Find the runner-up with LARGE](https://practiceexcelonline.com/practice/minmax/large-second-highest.md): In E2, use LARGE to find the second-highest score in B2:B7. - [The spread between highest and lowest](https://practiceexcelonline.com/practice/minmax/max-minus-min-spread.md): In E1, work out how far apart the quotes are, the highest minus the lowest. Subtract MIN from MAX in one formula. - [The basics: ROUND, ROUNDUP and ROUNDDOWN](https://practiceexcelonline.com/practice/round.md): 4 lessons - [Round a price to two decimals](https://practiceexcelonline.com/practice/round/round-two-decimals.md): In C2, add 25% VAT to the price in B2 (multiply by 1.25) and use ROUND to round the result to 2 decimals. - [Always round up with ROUNDUP](https://practiceexcelonline.com/practice/round/roundup-boxes-needed.md): In C2, work out how many boxes are needed for the items in A2 when each box holds B2. A part-full box is still a box, so use ROUNDUP with 0 decimals. - [Round to the nearest hundred](https://practiceexcelonline.com/practice/round/round-nearest-hundred.md): In C2, use ROUND to round the visitor count in B2 to the nearest hundred. A negative number of decimals rounds to the left of the decimal point. - [Count only whole teams with ROUNDDOWN](https://practiceexcelonline.com/practice/round/rounddown-full-teams.md): In C2, work out how many full teams of B2 people you can make from the A2 people who signed up. A team that isn't full doesn't count, so use ROUNDDOWN with 0 decimals. - [The basics: Relative and absolute references](https://practiceexcelonline.com/practice/absolute-references.md): 3 lessons - [Lock a rate cell with $](https://practiceexcelonline.com/practice/absolute-references/absolute-reference-dollar-sign.md): In C2, work out the tax on the price in B2 with the rate in F1. Lock F1 with $ so the formula can be filled down: =B2*$F$1. - [A price grid with mixed references](https://practiceexcelonline.com/practice/absolute-references/mixed-references.md): In E2, multiply the price in B2 by the units in E1 so the formula can be filled across to G2 and down to row 4: lock the column of the price and the row of the units, =$B2*E$1. - [A running total with a growing range](https://practiceexcelonline.com/practice/absolute-references/running-total.md): In C2, start a running total that can be filled down to C7: SUM a range that starts at a locked $B$2 and ends at a relative B2. - [The basics: Percentages](https://practiceexcelonline.com/practice/percentages.md): 4 lessons - [Percent change from last year](https://practiceexcelonline.com/practice/percentages/percent-change.md): In D2, work out the change in coffee sales from 2023 to 2024 as a decimal: the difference divided by the 2023 figure, (C2-B2)/B2. - [Add a markup to a cost price](https://practiceexcelonline.com/practice/percentages/add-percentage-markup.md): In D2, work out the selling price of the mug: the cost in B2 plus the markup in C2, which is a percentage of the cost. Use B2*(1+C2). - [Sale price after a discount](https://practiceexcelonline.com/practice/percentages/price-after-discount.md): In D2, work out the sale price of the jacket: the price in B2 less the discount in C2, B2*(1-C2). - [Find the price before VAT](https://practiceexcelonline.com/practice/percentages/price-before-vat.md): The prices in column B include 25% VAT. In C2, work out the printer's price before VAT: divide by 1 plus the rate in F1, B2/(1+$F$1). - [Logic: IF](https://practiceexcelonline.com/practice/if.md): 5 lessons - [Pass or fail with IF](https://practiceexcelonline.com/practice/if/if-pass-fail.md): The passing score is 50. In C2, use IF to show "Pass" when the score in B2 is 50 or more, and "Fail" otherwise. - [Calculate a bonus with IF](https://practiceexcelonline.com/practice/if/if-calculate-bonus.md): Sellers with sales over 1000 get a 10% bonus; everyone else gets 0. In C2, use IF to calculate the bonus for B2. - [Three outcomes with nested IF](https://practiceexcelonline.com/practice/if/nested-if.md): Grade the score in B2: 90 or more is "A", 70 or more is "B", anything lower is "C". In C2, put a second IF inside the first. - [Flag missing values with ISBLANK](https://practiceexcelonline.com/practice/if/if-isblank-missing.md): In D2, show "Missing" when the hours in B2 are blank, and otherwise the pay, B2*C2. Use IF with ISBLANK. - [Compare two cells with IF](https://practiceexcelonline.com/practice/if/if-compare-two-cells.md): In D2, show "Over budget" if the amount spent in C2 is more than the budget in B2, and "OK" if not. - [Logic: AND and OR](https://practiceexcelonline.com/practice/logical.md): 4 lessons - [Two conditions with AND](https://practiceexcelonline.com/practice/logical/if-and.md): A seller gets a bonus only with at least 5 deals AND a rating of at least 4.5. In D2, use IF with AND to show "Yes" or "No". - [Either condition with OR](https://practiceexcelonline.com/practice/logical/if-or.md): Shipping is free for Gold customers OR for orders of 500 or more. In D2, use IF with OR to show "Yes" or "No". - [AND and OR in one test](https://practiceexcelonline.com/practice/logical/and-or-combined.md): Delivery is free only inside DK, and then only for orders of 500 or more OR for members. In E2, use IF with AND, and OR inside the AND, to show "Free" or "Paid". - [Check a value is within limits](https://practiceexcelonline.com/practice/logical/and-between-two-limits.md): In E2, show "OK" if the measurement in B2 is between the limits in C2 and D2, both included, and "Check" if not. Both tests must be true, so use AND inside IF. - [Logic: IFS and SWITCH](https://practiceexcelonline.com/practice/ifs-switch.md): 4 lessons - [Grades with IFS](https://practiceexcelonline.com/practice/ifs-switch/ifs-grades.md): Grade the score in B2 with IFS: 90 or more is "A", 70 or more is "B", and anything else is "C". Use TRUE as the last test. - [Shipping price bands with IFS](https://practiceexcelonline.com/practice/ifs-switch/ifs-price-bands.md): Shipping costs 45 up to 1 kg, 69 up to 5 kg and 110 for anything heavier. In C2, use IFS to find the price for the weight in B2. - [Turn codes into names with SWITCH](https://practiceexcelonline.com/practice/ifs-switch/switch-codes-to-names.md): In C2, use SWITCH to turn the code in B2 into a name: "N" is "North", "S" is "South", and any other code is "Other". - [Commission rates with IFS](https://practiceexcelonline.com/practice/ifs-switch/ifs-commission-rates.md): Reps earn 10% commission on sales of 10,000 or more, 5% from 5,000 and nothing below that. In C2, multiply the sales in B2 by the rate IFS picks: B2*IFS(B2>=10000,0.1,B2>=5000,0.05,TRUE,0). - [Conditional sums and counts: COUNTIF](https://practiceexcelonline.com/practice/countif.md): 5 lessons - [COUNTIF with a text condition](https://practiceexcelonline.com/practice/countif/countif-text.md): In D2, use COUNTIF to count how many orders in B2:B8 came from Copenhagen. - [COUNTIF with a number condition](https://practiceexcelonline.com/practice/countif/countif-greater-than.md): In D2, use COUNTIF to count the invoices in B2:B8 of 1500 or more. - [COUNTIF with the limit in a cell](https://practiceexcelonline.com/practice/countif/countif-criteria-from-a-cell.md): In E2, count the products in B2:B7 with stock below the reorder level in E1. Join "<" and E1 with &, so the count follows when E1 changes. - [Count everything except one value](https://practiceexcelonline.com/practice/countif/countif-not-equal.md): In E1, count the tickets in B2:B8 whose status is anything but Closed. "Not equal to" is <>, so the criterion is "<>Closed". - [Count with a wildcard](https://practiceexcelonline.com/practice/countif/countif-wildcard-contains.md): In D1, count the products in A2:A8 with the word Chair anywhere in the name. * stands for any text, so the criterion is "*Chair*". - [Conditional sums and counts: SUMIF](https://practiceexcelonline.com/practice/sumif.md): 5 lessons - [SUMIF with a text criterion](https://practiceexcelonline.com/practice/sumif/sumif-text-criteria.md): In D2, use SUMIF to total the Units sold of Apple. - [SUMIF with a number condition](https://practiceexcelonline.com/practice/sumif/sumif-greater-than.md): In D2, use SUMIF to total the amounts greater than 100. - [SUMIF with a separate sum_range](https://practiceexcelonline.com/practice/sumif/sumif-with-sum-range.md): In E2, use SUMIF to total the Amount of orders with more than 3 units. - [SUMIF with the criterion in a cell](https://practiceexcelonline.com/practice/sumif/sumif-criteria-from-a-cell.md): In E2, total the Sales of the rep named in D2. Point SUMIF at D2 instead of typing the name, so the total follows when D2 changes. - [SUMIF with a wildcard](https://practiceexcelonline.com/practice/sumif/sumif-wildcard-contains.md): In E1, total the sales of every product with Chair anywhere in its name. Use * on both sides of the word: SUMIF(A2:A8,"*Chair*",B2:B8). - [Conditional sums and counts: AVERAGEIF and AVERAGEIFS](https://practiceexcelonline.com/practice/averageif.md): 3 lessons - [Average one group with AVERAGEIF](https://practiceexcelonline.com/practice/averageif/averageif.md): In D2, use AVERAGEIF to find the average order value of the Online orders. - [Average with two conditions with AVERAGEIFS](https://practiceexcelonline.com/practice/averageif/averageifs-two-conditions.md): In F1, find the average sale of Shoes in the North region. AVERAGEIFS starts with the range to average, then each range with its condition. - [Leave zeros out of an average](https://practiceexcelonline.com/practice/averageif/average-without-zeros.md): The store is closed on Mondays and Sundays and records 0 sales. In E1, find the average sales of the days it was open: AVERAGEIF with the condition ">0". - [Conditional sums and counts: SUMIFS](https://practiceexcelonline.com/practice/sumifs.md): 4 lessons - [SUMIFS with two conditions](https://practiceexcelonline.com/practice/sumifs/sumifs-two-conditions.md): In E2, use SUMIFS to total the Sales of Shoes in the North region. - [SUMIFS with the conditions in cells](https://practiceexcelonline.com/practice/sumifs/sumifs-criteria-from-cells.md): In G2, use SUMIFS to total the Sales for the region in E2 and the product in F2. Point at the cells instead of typing the words. - [SUMIFS between two dates](https://practiceexcelonline.com/practice/sumifs/sumifs-between-two-dates.md): In F3, total the amounts of the orders dated from F1 to F2, both days included. Give SUMIFS the date column twice: ">="&F1 and "<="&F2. - [SUMIFS that leaves one value out](https://practiceexcelonline.com/practice/sumifs/sumifs-not-equal.md): In F1, total the North orders that were not returned. Give SUMIFS two conditions: the region "North" and the status "<>Returned". - [Conditional sums and counts: COUNTIFS](https://practiceexcelonline.com/practice/countifs.md): 4 lessons - [COUNTIFS with two conditions](https://practiceexcelonline.com/practice/countifs/countifs-two-conditions.md): In E2, use COUNTIFS to count the North orders with Sales over 400. - [Count between two numbers with COUNTIFS](https://practiceexcelonline.com/practice/countifs/countifs-between-two-numbers.md): In E2, use COUNTIFS to count the people in B2:B8 aged 18 to 30, both ages included. Use the same range twice, once for each limit. - [Count the orders between two dates](https://practiceexcelonline.com/practice/countifs/countifs-between-two-dates.md): In F3, count the orders dated from F1 to F2, both days included. Use the date column twice in COUNTIFS and join each operator to its cell: ">="&F1 and "<="&F2. - [COUNTIFS with the conditions in cells](https://practiceexcelonline.com/practice/countifs/countifs-criteria-from-cells.md): In F3, count the reps from the region in F1 with sales of at least the amount in F2. Point at both cells: F1 as it is, and F2 joined to ">=". - [Conditional sums and counts: Summary tables](https://practiceexcelonline.com/practice/summary-tables.md): 6 lessons - [Total per region, ready to fill down](https://practiceexcelonline.com/practice/summary-tables/summary-table-sumif.md): In F2, total the Sales for the region in E2 with SUMIF. Lock the data ranges with $ so the formula can be filled down to F4: =SUMIF($A$2:$A$9,E2,$C$2:$C$9). - [A cross-table with mixed references](https://practiceexcelonline.com/practice/summary-tables/cross-table-sumifs.md): Build a table with regions down and products across. In F3, use SUMIFS for the region in E3 and the product in F2, with $E3 and F$2 so the formula can be filled down and across. - [Orders per region with COUNTIF](https://practiceexcelonline.com/practice/summary-tables/summary-table-countif.md): In F2, count the orders for the region in E2 with COUNTIF. Lock the region range with $ so the formula can be filled down. - [Average order per region](https://practiceexcelonline.com/practice/summary-tables/summary-table-averageif.md): In F2, find the average Sales for the region in E2 with AVERAGEIF, with the data ranges locked so the formula can be filled down. - [Each region's share of the total](https://practiceexcelonline.com/practice/summary-tables/summary-table-share-of-total.md): In F2, work out the share of all sales for the region in E2, as a decimal like 0.25: the region's SUMIF total divided by SUM($C$2:$C$9). - [Count a cross-table with COUNTIFS](https://practiceexcelonline.com/practice/summary-tables/cross-table-countifs.md): Count the orders with regions down and products across. In F3, use COUNTIFS for the region in E3 and the product in F2, with $E3 and F$2 so the formula can be filled down and across. - [Conditional sums and counts: SUMPRODUCT](https://practiceexcelonline.com/practice/sumproduct.md): 3 lessons - [Order total with SUMPRODUCT](https://practiceexcelonline.com/practice/sumproduct/sumproduct-order-total.md): In F1, work out the order total in one formula without a helper column. Use SUMPRODUCT to multiply the units by the prices row by row and add up the results. - [A weighted average with SUMPRODUCT](https://practiceexcelonline.com/practice/sumproduct/weighted-average.md): In F1, work out the weighted average score, where each score counts as many times as its weight. Divide the SUMPRODUCT of scores and weights by the SUM of the weights. - [Revenue for one region with SUMPRODUCT](https://practiceexcelonline.com/practice/sumproduct/sumproduct-with-condition.md): In F1, work out the revenue (units × price) for the North rows only, in one formula. Multiply a test on the region by the two columns inside SUMPRODUCT: SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7). - [Lookups: VLOOKUP](https://practiceexcelonline.com/practice/vlookup.md): 4 lessons - [Look up a price with VLOOKUP](https://practiceexcelonline.com/practice/vlookup/vlookup-exact-match.md): In G2, use VLOOKUP to find the Price of the code in F2. Ask for an exact match. - [VLOOKUP a number key](https://practiceexcelonline.com/practice/vlookup/vlookup-number-id.md): In G2, use VLOOKUP to return the Salary of the employee whose ID is in F2. - [VLOOKUP with an approximate match](https://practiceexcelonline.com/practice/vlookup/vlookup-approximate-match.md): In E2, find the commission rate for the sales in D2. The table lists where each band starts, so use VLOOKUP with TRUE for an approximate match. - [Bring prices into an order list](https://practiceexcelonline.com/practice/vlookup/vlookup-fill-down-locked-table.md): In C2, look up the price of the code in B2 in the price table E2:F5. Lock the table with $ so the formula can be filled down the order list: VLOOKUP(B2,$E$2:$F$5,2,FALSE). - [Lookups: INDEX and MATCH](https://practiceexcelonline.com/practice/index-match.md): 4 lessons - [Find a position with MATCH](https://practiceexcelonline.com/practice/index-match/match-function.md): In E2, use MATCH to find the position of the product in D2 within A2:A7. Add 0 as the last argument for an exact match. - [Look up a price with INDEX and MATCH](https://practiceexcelonline.com/practice/index-match/index-match.md): In F2, return the Price of the product in E2. Use MATCH to find its row in A2:A7 and INDEX to pick that row from C2:C7. - [Look to the left with INDEX and MATCH](https://practiceexcelonline.com/practice/index-match/index-match-left-lookup.md): The IDs are in column C, to the right of the names. In F2, return the Name for the ID in E2 with INDEX and MATCH. - [A two-way lookup with INDEX and two MATCHes](https://practiceexcelonline.com/practice/index-match/index-match-match-two-way.md): In H3, find the sales for the product in H1 and the region in H2. INDEX the numbers B2:E5 with one MATCH for the row and one for the column: INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0)). - [Lookups: XLOOKUP](https://practiceexcelonline.com/practice/xlookup.md): 4 lessons - [Look up a price with XLOOKUP](https://practiceexcelonline.com/practice/xlookup/xlookup.md): In F2, use XLOOKUP to return the Price for the SKU in E2: look for E2 in A2:A6 and return from C2:C6. - [XLOOKUP with a not-found value](https://practiceexcelonline.com/practice/xlookup/xlookup-if-not-found.md): P-109 is not in the list. In F2, use XLOOKUP to return the price for E2, and use its fourth argument to show "Not found" instead of #N/A. - [Find the latest match with XLOOKUP](https://practiceexcelonline.com/practice/xlookup/xlookup-last-match.md): The price list grows at the bottom each time a price changes. In F2, find the current price of the product in F1, the last match in the list. Search from the bottom with search_mode -1, the sixth argument: XLOOKUP(F1,B2:B8,C2:C8,,0,-1). - [The smallest box that fits with XLOOKUP](https://practiceexcelonline.com/practice/xlookup/xlookup-next-larger.md): In C2, pick the smallest box that holds the volume in B2. If there is no exact match, XLOOKUP should take the next larger size: match_mode 1, the fifth argument, XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,1). - [Lookups: IFERROR](https://practiceexcelonline.com/practice/iferror.md): 3 lessons - [Hide #N/A with IFERROR](https://practiceexcelonline.com/practice/iferror/iferror-vlookup.md): IS is not in the table, so a plain VLOOKUP shows #N/A. In E2, wrap VLOOKUP(D2,A2:B6,2,FALSE) in IFERROR to show "Unknown" instead. - [Avoid #DIV/0! with IFERROR](https://practiceexcelonline.com/practice/iferror/iferror-div0.md): The lamp has not sold yet, so B2/C2 gives #DIV/0!. In D2, wrap B2/C2 in IFERROR to show 0 instead. - [Catch only "not found" with IFNA](https://practiceexcelonline.com/practice/iferror/ifna-vlookup-not-found.md): E-109 is a new hire who isn't in the list yet. In E2, look up the department for D2 with VLOOKUP and wrap it in IFNA to show "Not listed" instead of #N/A. - [Lookups: FILTER](https://practiceexcelonline.com/practice/filter-function.md): 3 lessons - [Keep the rows for one region with FILTER](https://practiceexcelonline.com/practice/filter-function/filter-one-condition.md): In E2, list the rep and sales of every North row. FILTER the columns you want, B2:C7, by a test on the region column: A2:A7="North". - [FILTER with two conditions](https://practiceexcelonline.com/practice/filter-function/filter-two-conditions.md): In E2, list the rep and sales of the North rows with sales over 300. Multiply the two tests so a row must pass both: (A2:A9="North")*(C2:C9>300). - [A message when FILTER finds nothing](https://practiceexcelonline.com/practice/filter-function/filter-if-empty.md): In E4, list the reps whose sales reached the target in F1. Nobody did this month, so give FILTER a third argument to show "No one" instead of an error. - [Lookups: SORT and UNIQUE](https://practiceexcelonline.com/practice/sort-and-unique.md): 3 lessons - [Sort a table by sales with SORT](https://practiceexcelonline.com/practice/sort-and-unique/sort-function.md): In D2, return the table A2:B6 sorted by sales, highest first. SORT takes the range, the column to sort by (2) and the order (-1 for descending). - [List each customer once with UNIQUE](https://practiceexcelonline.com/practice/sort-and-unique/unique-function.md): In D2, list every customer in B2:B8 once, without the repeats, with UNIQUE. - [A sorted list without duplicates](https://practiceexcelonline.com/practice/sort-and-unique/sort-unique-list.md): In D2, list every rep in B2:B9 once, sorted from A to Z. Put UNIQUE inside SORT. - [Text: TRIM, PROPER and UPPER](https://practiceexcelonline.com/practice/trim-proper-upper.md): 4 lessons - [Remove extra spaces with TRIM](https://practiceexcelonline.com/practice/trim-proper-upper/trim-extra-spaces.md): In B2, use TRIM to remove the extra spaces from the name in A2. - [Fix the case of names with PROPER](https://practiceexcelonline.com/practice/trim-proper-upper/proper-capitalize-names.md): In B2, use PROPER to write the name in A2 with a capital first letter in each word. - [Clean a code with UPPER and TRIM](https://practiceexcelonline.com/practice/trim-proper-upper/upper-trim-clean-codes.md): In B2, clean up the code in A2 so it has no extra spaces and is all capitals. Put TRIM inside UPPER. - [Fix messy names with PROPER and TRIM](https://practiceexcelonline.com/practice/trim-proper-upper/proper-trim-clean-names.md): The name in A2 has extra spaces and the wrong capitals. In B2, clean it up to "Maria Lopez". Put TRIM inside PROPER. - [Text: LEFT, RIGHT and MID](https://practiceexcelonline.com/practice/left-right-mid.md): 4 lessons - [Take the prefix with LEFT](https://practiceexcelonline.com/practice/left-right-mid/left-first-characters.md): The first three letters of the product code in A2 name the warehouse. In B2, use LEFT to get them. - [Take the last digits with RIGHT](https://practiceexcelonline.com/practice/left-right-mid/right-last-characters.md): In B2, use RIGHT to get the last four characters of the invoice in A2, the running number. - [Take the year from the middle with MID](https://practiceexcelonline.com/practice/left-right-mid/mid-text-from-middle.md): The year in the invoice in A2 starts at character 5 and is 4 characters long. In B2, use MID to get it. - [Drop a prefix with RIGHT and LEN](https://practiceexcelonline.com/practice/left-right-mid/right-len-remove-prefix.md): Every order ID starts with a 3-letter channel code, but the numbers have different lengths. In B2, take everything after the first 3 characters with RIGHT, and use LEN(A2)-3 for how many. - [Text: FIND and SEARCH](https://practiceexcelonline.com/practice/find-search.md): 5 lessons - [Find the @ in an email with FIND](https://practiceexcelonline.com/practice/find-search/find-position-of-character.md): In B2, use FIND to get the position of the @ in the email address in A2. - [Take the name before the @](https://practiceexcelonline.com/practice/find-search/left-find-text-before-character.md): In B2, get the part of the email in A2 before the @. Use LEFT, with FIND to work out how many characters to take. - [Take the domain after the @](https://practiceexcelonline.com/practice/find-search/mid-find-text-after-character.md): In B2, get the domain, the part of the email in A2 after the @. Use MID to start one character after the @, and LEN for the number of characters. - [Check if a text contains a word](https://practiceexcelonline.com/practice/find-search/search-isnumber-contains.md): In B2, show TRUE if the product name in A2 contains "pro" in any case. Use SEARCH inside ISNUMBER. - [Count the words in a cell](https://practiceexcelonline.com/practice/find-search/count-words-in-cell.md): In B2, count the words in A2. There is one more word than there are spaces, and LEN(A2) minus the length without spaces, LEN(SUBSTITUTE(A2," ","")), is the number of spaces. - [Text: Joining text with & and TEXTJOIN](https://practiceexcelonline.com/practice/join-text.md): 4 lessons - [Join first and last name with &](https://practiceexcelonline.com/practice/join-text/join-names-ampersand.md): In C2, join the first name in A2 and the last name in B2 with a space between them. Use the & operator. - [Build an email address](https://practiceexcelonline.com/practice/join-text/build-email-address.md): In C2, build the email address first.last@northwind.com in small letters from A2 and B2. Join the parts with & and wrap the whole thing in LOWER. - [Join an address with TEXTJOIN](https://practiceexcelonline.com/practice/join-text/textjoin-address.md): In E2, join A2:D2 into one address with a comma and a space between the parts. Use TEXTJOIN and skip the empty Suite cell. - [Build an ID from a department and a number](https://practiceexcelonline.com/practice/join-text/build-id-left-upper.md): Badge IDs are the first 3 letters of the department in capitals, a dash and the employee number, like MAR-1042. In C2, build the ID with LEFT, UPPER and &. - [Text: SUBSTITUTE, TEXTBEFORE and TEXTAFTER](https://practiceexcelonline.com/practice/substitute-textbefore-textafter.md): 4 lessons - [Remove the dashes with SUBSTITUTE](https://practiceexcelonline.com/practice/substitute-textbefore-textafter/substitute-remove-characters.md): In B2, use SUBSTITUTE to remove every dash from the phone number in A2. Replace "-" with an empty text, "". - [Take the first name with TEXTBEFORE](https://practiceexcelonline.com/practice/substitute-textbefore-textafter/textbefore-first-name.md): In B2, use TEXTBEFORE to get the text in A2 before the first space. - [Flip "Last, First" with TEXTAFTER](https://practiceexcelonline.com/practice/substitute-textbefore-textafter/textafter-flip-names.md): In B2, turn the name in A2 into "Anna Berg". Join TEXTAFTER and TEXTBEFORE around ", " with a space between them. - [Take the text between parentheses](https://practiceexcelonline.com/practice/substitute-textbefore-textafter/text-between-parentheses.md): In B2, take the department between the parentheses in A2. Use TEXTAFTER to take what comes after "(", and TEXTBEFORE around it to stop at ")". - [Dates: Dates and DATE](https://practiceexcelonline.com/practice/dates.md): 5 lessons - [Days between two dates](https://practiceexcelonline.com/practice/dates/days-between-dates.md): In C2, work out how many days the delivery took. Subtract the order date in A2 from the delivery date in B2. - [Add days to get a due date](https://practiceexcelonline.com/practice/dates/add-days-to-date.md): In C2, work out the due date of the invoice. Add the payment terms in B2 to the invoice date in A2. - [Build a date with DATE](https://practiceexcelonline.com/practice/dates/date-function.md): The year, month and day of a date came from a system in three columns. In D2, use DATE to turn A2, B2 and C2 into one date. - [Get the month with MONTH](https://practiceexcelonline.com/practice/dates/month-from-date.md): To total sales by month you need the month number of each sale. In B2, use MONTH to get it from the date in A2. - [Get the quarter from a date](https://practiceexcelonline.com/practice/dates/quarter-from-date.md): In B2, find the quarter of the sale date in A2, a number from 1 to 4. Divide the month by 3 and round up: ROUNDUP(MONTH(A2)/3,0). - [Dates: EDATE and EOMONTH](https://practiceexcelonline.com/practice/edate-eomonth.md): 4 lessons - [Contract end date with EDATE](https://practiceexcelonline.com/practice/edate-eomonth/edate-add-months.md): In C2, use EDATE to find the date the number of months in B2 after the start date in A2. - [Last day of the month with EOMONTH](https://practiceexcelonline.com/practice/edate-eomonth/eomonth-end-of-month.md): In B2, use EOMONTH to find the last day of the month of the invoice date in A2. - [First day of next month](https://practiceexcelonline.com/practice/edate-eomonth/first-day-of-next-month.md): A subscription starts on the first day of the month after it is signed. In B2, find that date from A2. Take the end of the signing month with EOMONTH and add one day. - [Days in the month with DAY and EOMONTH](https://practiceexcelonline.com/practice/edate-eomonth/days-in-month.md): To prorate the first month's rent you need the number of days in the move-in month. In B2, take the day number of the month's last day, DAY(EOMONTH(A2,0)). - [Dates: NETWORKDAYS, WORKDAY and WEEKDAY](https://practiceexcelonline.com/practice/networkdays-workday.md): 5 lessons - [Count working days with NETWORKDAYS](https://practiceexcelonline.com/practice/networkdays-workday/networkdays-working-days.md): In C2, use NETWORKDAYS to count the working days, Monday to Friday, from the start date in A2 to the end date in B2. - [Working days around Easter](https://practiceexcelonline.com/practice/networkdays-workday/networkdays-holidays.md): The office is closed on the days in E2:E4. In C2, count the working days from A2 to B2 with NETWORKDAYS, and give it E2:E4 as the holidays. - [Delivery date with WORKDAY](https://practiceexcelonline.com/practice/networkdays-workday/workday-delivery-date.md): Delivery takes the number of working days in B2. In C2, use WORKDAY to find the delivery date for the order date in A2. - [Day of the week with WEEKDAY](https://practiceexcelonline.com/practice/networkdays-workday/weekday-day-of-week.md): In B2, use WEEKDAY with 2 as the second argument to number the day of the week of A2, with Monday as 1. - [Delivery date over the holidays](https://practiceexcelonline.com/practice/networkdays-workday/workday-holidays.md): The office is closed on the days in E2:E5. In C2, find the delivery date B2 working days after the order date in A2. Use WORKDAY and give it E2:E5 as the holidays. - [Dates: DATEDIF](https://practiceexcelonline.com/practice/datedif.md): 3 lessons - [Work out an age with DATEDIF](https://practiceexcelonline.com/practice/datedif/datedif-age.md): In C2, use DATEDIF with the unit "y" to work out the age in whole years on the date in B2 of someone born on A2. - [Months of service with DATEDIF](https://practiceexcelonline.com/practice/datedif/datedif-months-of-service.md): In C2, use DATEDIF to count the whole months of service from the hire date in A2 to the date in B2. - [Years and months of service as text](https://practiceexcelonline.com/practice/datedif/datedif-years-and-months.md): In C2, show the service from A2 to B2 as "6 years, 8 months". Join DATEDIF with "y" for the whole years, the text " years, ", DATEDIF with "ym" for the months left over and " months". - [Dates: Week numbers and weekends](https://practiceexcelonline.com/practice/week-numbers.md): 3 lessons - [Week numbers with ISOWEEKNUM](https://practiceexcelonline.com/practice/week-numbers/isoweeknum-week-number.md): In B2, find the ISO week number of the delivery date in A2, the week number used on calendars in Europe, with ISOWEEKNUM. - [Weekend or weekday](https://practiceexcelonline.com/practice/week-numbers/weekend-or-weekday.md): In B2, show "Weekend" if the date in A2 falls on a Saturday or Sunday and "Weekday" if not. With WEEKDAY(A2,2), Monday is 1, so the weekend is anything over 5. - [The Monday that starts the week](https://practiceexcelonline.com/practice/week-numbers/monday-of-the-week.md): In B2, find the Monday of the week the order date in A2 falls in. Go back as many days as WEEKDAY(A2,2) says, then forward one: A2-WEEKDAY(A2,2)+1. - [Finance: Loans: PMT and NPER](https://practiceexcelonline.com/practice/loan-payments.md): 3 lessons - [Monthly loan payment with PMT](https://practiceexcelonline.com/practice/loan-payments/pmt-loan-payment.md): In B5, work out the monthly payment on the loan with PMT. The rate and the number of payments must both be monthly: PMT(B3/12,B4*12,-B2). - [Months to pay off a loan with NPER](https://practiceexcelonline.com/practice/loan-payments/nper-months-to-pay-off.md): In B5, work out how many monthly payments of 500 it takes to pay off the loan, with NPER. Use the monthly rate and give the payment and the loan opposite signs: NPER(B3/12,-B4,B2). - [Total interest on a loan](https://practiceexcelonline.com/practice/loan-payments/total-interest-on-a-loan.md): In B5, work out how much interest the loan costs in total: all the monthly payments added up, minus the amount borrowed. That is PMT(B3/12,B4*12,-B2)*B4*12-B2. - [Finance: Investments: FV, NPV and IRR](https://practiceexcelonline.com/practice/investments.md): 3 lessons - [What savings grow to with FV](https://practiceexcelonline.com/practice/investments/fv-savings.md): In B5, work out what the savings grow to with FV: the monthly rate, the number of months and the monthly deposit as money going out, FV(B3/12,B4*12,-B2). - [Net present value with NPV](https://practiceexcelonline.com/practice/investments/npv-net-present-value.md): In E2, work out the project's net present value at the rate in E1. NPV discounts every value it gets, so give it years 1 to 4 and add the year 0 investment after: NPV(E1,B3:B6)+B2. - [Rate of return with IRR](https://practiceexcelonline.com/practice/investments/irr-rate-of-return.md): In E1, work out the project's internal rate of return with IRR. Give it every cash flow, starting with the year 0 investment. - [Keyboard shortcuts: Moving and selecting](https://practiceexcelonline.com/practice/moving-and-selecting.md): 3 lessons - [Jump to the end of your data](https://practiceexcelonline.com/practice/moving-and-selecting/jump-to-the-end-of-your-data.md): Move around a list of invoices with the keyboard instead of scrolling. - [Select a whole table](https://practiceexcelonline.com/practice/moving-and-selecting/select-a-whole-table.md): Select a table of any size with two shortcuts. - [Select rows, columns and everything](https://practiceexcelonline.com/practice/moving-and-selecting/select-rows-and-columns.md): Select a whole row, a whole column and the whole sheet from the keyboard. - [Keyboard shortcuts: Editing and filling](https://practiceexcelonline.com/practice/editing-and-filling.md): 5 lessons - [Edit a cell without retyping it](https://practiceexcelonline.com/practice/editing-and-filling/edit-a-cell.md): Open a cell for editing, back out of it, and move on with Enter and Tab. - [Fill a formula down and right](https://practiceexcelonline.com/practice/editing-and-filling/fill-down-and-right.md): Copy a formula to the rows below and the column next to it without retyping it. - [AutoSum, undo and redo](https://practiceexcelonline.com/practice/editing-and-filling/autosum-undo-redo.md): Add a total with one shortcut, then undo and redo it. - [Lock a reference with F4](https://practiceexcelonline.com/practice/editing-and-filling/lock-reference-f4.md): Fix a formula so it can be filled down, without retyping it. - [Fill every selected cell with Ctrl+Enter](https://practiceexcelonline.com/practice/editing-and-filling/ctrl-enter-fill-selection.md): Put one formula into a whole selection at once. ## Excel functions - [ABS](https://practiceexcelonline.com/functions/abs.md): Returns the absolute value of a number, the number without its sign. -25 becomes 25 and 25 stays 25. - [AND](https://practiceexcelonline.com/functions/and.md): Returns TRUE only when every condition is TRUE. Usually used as the test inside IF. - [AVERAGE](https://practiceexcelonline.com/functions/average.md): Returns the mean: the sum of the numbers divided by how many there are. Blank cells and text are skipped. - [AVERAGEIF](https://practiceexcelonline.com/functions/averageif.md): Returns the average of the cells that meet one condition. - [AVERAGEIFS](https://practiceexcelonline.com/functions/averageifs.md): Returns the average of the cells that meet every one of several conditions. The average range comes first. - [CEILING.MATH](https://practiceexcelonline.com/functions/ceiling-math.md): Rounds a number up to the nearest multiple you choose, such as the next 5, 10 or 0.25. Without a multiple it rounds up to the next whole number. - [CHAR](https://practiceexcelonline.com/functions/char.md): Returns the character for a code number. CHAR(65) is A, and CHAR(10) is a line break, the usual way to put line breaks into a formula. - [CHOOSE](https://practiceexcelonline.com/functions/choose.md): Returns the value at a numbered position in a list of arguments. - [COLUMN](https://practiceexcelonline.com/functions/column.md): Returns the column number of a cell, 1 for A, 2 for B and 27 for AA. Left empty, it returns the column of the cell the formula is in. - [CONCAT](https://practiceexcelonline.com/functions/concat.md): Joins texts and ranges into one text. The & operator does the same for single cells. - [CONCATENATE](https://practiceexcelonline.com/functions/concatenate.md): Joins up to 255 texts into one. In Excel it takes single cells, not ranges; CONCAT and the & operator do the same job in new sheets. - [CORREL](https://practiceexcelonline.com/functions/correl.md): Returns the correlation coefficient of two ranges, from -1 to 1. Close to 1 means the two rise together, close to -1 that one falls as the other rises, and close to 0 no straight-line link. - [COUNT](https://practiceexcelonline.com/functions/count.md): Counts the cells that hold numbers (dates count too). Text, blanks and errors are not counted. - [COUNTA](https://practiceexcelonline.com/functions/counta.md): Counts the cells that are not empty: numbers, text, logical values and errors. - [COUNTBLANK](https://practiceexcelonline.com/functions/countblank.md): Counts the empty cells in a range. A formula that returns "" also counts as empty. - [COUNTIF](https://practiceexcelonline.com/functions/countif.md): Counts the cells in a range that meet one condition. Text conditions ignore upper and lower case. - [COUNTIFS](https://practiceexcelonline.com/functions/countifs.md): Counts the rows that meet every one of several conditions. Use the same range twice to count between two limits. - [DATE](https://practiceexcelonline.com/functions/date.md): Builds a date from a year, month and day. Months and days outside the normal range roll over: month 14 is February next year. - [DATEDIF](https://practiceexcelonline.com/functions/datedif.md): Counts the whole years, months or days between two dates, which is how you work out an age or a length of service. Excel leaves it out of the function list, but it works. - [DATEVALUE](https://practiceexcelonline.com/functions/datevalue.md): Converts a date written as text, such as "2024-03-15", into a date serial number Excel can calculate with and format. - [DAY](https://practiceexcelonline.com/functions/day.md): Returns the day of the month of a date, a number from 1 to 31. - [DAYS](https://practiceexcelonline.com/functions/days.md): Returns the number of days between two dates. The end date comes first, and the result is the same as subtracting the start date from the end date. - [EDATE](https://practiceexcelonline.com/functions/edate.md): Returns the same day a number of months before or after a date. In a shorter month it stops at the last day, so one month after Jan 31, 2024 is Feb 29. - [EOMONTH](https://practiceexcelonline.com/functions/eomonth.md): Returns the last day of the month a number of months before or after a date. 0 gives the end of the same month. - [FILTER](https://practiceexcelonline.com/functions/filter.md): Returns the rows of a range that meet a condition, as a list that spills into the cells below. - [FIND](https://practiceexcelonline.com/functions/find.md): Returns the position of one text inside another. FIND is case-sensitive and does not allow wildcards; it returns #VALUE! when nothing is found. - [FV](https://practiceexcelonline.com/functions/fv.md): Returns the future value of an investment with equal regular payments and a fixed interest rate. Money you pay in is negative, so the result is positive. - [HLOOKUP](https://practiceexcelonline.com/functions/hlookup.md): Looks for a value in the first row of a table and returns the value from another row in the same column. - [HOUR](https://practiceexcelonline.com/functions/hour.md): Returns the hour of a time as a number from 0 to 23. Times are fractions of a day, so 0.75 is 6:00 PM and HOUR(0.75) is 18. - [IF](https://practiceexcelonline.com/functions/if.md): Returns one value when a test is TRUE and another when it is FALSE. Put text results in double quotes. - [IFERROR](https://practiceexcelonline.com/functions/iferror.md): Returns a value you choose when a formula gives any error, and the formula result otherwise. - [IFNA](https://practiceexcelonline.com/functions/ifna.md): Returns a value you choose when a formula gives #N/A, and the formula result otherwise. Other errors still show. - [IFS](https://practiceexcelonline.com/functions/ifs.md): Checks several conditions in order and returns the value for the first one that is TRUE. Use TRUE as the last test for "everything else". - [INDEX](https://practiceexcelonline.com/functions/index.md): Returns the value at a given row and column position in a range. Paired with MATCH it becomes a lookup in any direction. - [INDIRECT](https://practiceexcelonline.com/functions/indirect.md): Turns text into a cell reference, such as "B3" or "Sheet2!A1", and returns what is in that cell. Use it to build a reference from other cells. - [INT](https://practiceexcelonline.com/functions/int.md): Rounds a number down to the nearest whole number. Negative numbers go further from zero: INT(-7.9) is -8. - [IRR](https://practiceexcelonline.com/functions/irr.md): Returns the internal rate of return of a series of cash flows, the discount rate at which their net present value is 0. - [ISBLANK](https://practiceexcelonline.com/functions/isblank.md): Returns TRUE when a cell is empty. A cell with a formula that returns "" is not empty. - [ISERROR](https://practiceexcelonline.com/functions/iserror.md): Returns TRUE if a value is any error, such as - [ISNA](https://practiceexcelonline.com/functions/isna.md): Returns TRUE if a value is the - [ISNUMBER](https://practiceexcelonline.com/functions/isnumber.md): Returns TRUE if a value is a number and FALSE for anything else, including text that only looks like a number. Dates and times are numbers too. - [ISTEXT](https://practiceexcelonline.com/functions/istext.md): Returns TRUE if a value is text and FALSE otherwise. Numbers stored as text count as text; empty cells do not. - [LARGE](https://practiceexcelonline.com/functions/large.md): Returns the k-th largest value in a range: k = 1 is the maximum, k = 2 the runner-up. - [LEFT](https://practiceexcelonline.com/functions/left.md): Returns the first characters of a text. Without num_chars it returns one character. - [LEN](https://practiceexcelonline.com/functions/len.md): Returns the number of characters in a text, spaces included. - [LOOKUP](https://practiceexcelonline.com/functions/lookup.md): Looks up a value in one row or column and returns the value in the same position from another. It always does an approximate match, so the lookup range must be sorted from smallest to largest. - [LOWER](https://practiceexcelonline.com/functions/lower.md): Converts text to small letters. - [MATCH](https://practiceexcelonline.com/functions/match.md): Returns the position of a value in a row or column. Use 0 as match_type for an exact match. - [MAX](https://practiceexcelonline.com/functions/max.md): Returns the largest number. Text and blank cells in a range are ignored. - [MEDIAN](https://practiceexcelonline.com/functions/median.md): Returns the middle value of a set of numbers. With an even count it averages the two in the middle. - [MID](https://practiceexcelonline.com/functions/mid.md): Returns a number of characters from the middle of a text, starting at a given position. - [MIN](https://practiceexcelonline.com/functions/min.md): Returns the smallest number. Text and blank cells in a range are ignored. - [MOD](https://practiceexcelonline.com/functions/mod.md): Returns the remainder after division. The result has the same sign as the divisor. - [MODE](https://practiceexcelonline.com/functions/mode.md): Returns the number that appears most often in a range. If no number repeats, it returns - [MONTH](https://practiceexcelonline.com/functions/month.md): Returns the month of a date as a number from 1 (January) to 12 (December). - [NETWORKDAYS](https://practiceexcelonline.com/functions/networkdays.md): Counts the working days (Monday to Friday) between two dates, both included, minus any holidays you list. - [NOT](https://practiceexcelonline.com/functions/not.md): Reverses a logical value: TRUE becomes FALSE and FALSE becomes TRUE. - [NOW](https://practiceexcelonline.com/functions/now.md): Returns the current date and time. It updates every time the sheet recalculates, so it is a live clock, not a timestamp. - [NPER](https://practiceexcelonline.com/functions/nper.md): Returns how many payment periods it takes to pay off a loan, or to reach a savings goal, with fixed payments and a fixed interest rate. - [NPV](https://practiceexcelonline.com/functions/npv.md): Returns the net present value of future cash flows at a discount rate. The first value is discounted one period, so a payment made today is added outside NPV. - [OFFSET](https://practiceexcelonline.com/functions/offset.md): Returns a reference a number of rows and columns away from a starting cell, optionally a whole range of a given height and width. Wrap it in SUM or AVERAGE to work with that range. - [OR](https://practiceexcelonline.com/functions/or.md): Returns TRUE when at least one condition is TRUE. - [PERCENTILE.INC](https://practiceexcelonline.com/functions/percentile-inc.md): Returns the value below which a given share of the data falls, such as the 90th percentile. The share k is a fraction from 0 to 1. - [PMT](https://practiceexcelonline.com/functions/pmt.md): Returns the payment per period on a loan with a fixed interest rate, paid off in equal payments. The result is negative because it is money going out. - [POWER](https://practiceexcelonline.com/functions/power.md): Raises a number to a power. POWER(2,3) is 2×2×2, which is 8, the same as =2^3. - [PRODUCT](https://practiceexcelonline.com/functions/product.md): Multiplies all the numbers you give it, ranges included. Empty cells and text in a range are skipped. - [PROPER](https://practiceexcelonline.com/functions/proper.md): Capitalizes the first letter of each word and makes the rest small. - [PV](https://practiceexcelonline.com/functions/pv.md): Returns the present value, what a series of equal future payments, or one future amount, is worth today at a given interest rate. - [RAND](https://practiceexcelonline.com/functions/rand.md): Returns a random decimal number from 0 up to, but not including, 1. It changes every time the sheet recalculates. - [RANDBETWEEN](https://practiceexcelonline.com/functions/randbetween.md): Returns a random whole number between a bottom and a top number, both included. It changes every time the sheet recalculates. - [RANK](https://practiceexcelonline.com/functions/rank.md): Returns the rank of a number in a list, 1 for the largest by default. Equal numbers share a rank. - [RATE](https://practiceexcelonline.com/functions/rate.md): Returns the interest rate per period of a loan or investment, from the number of payments, the payment and the amount. Multiply by 12 to get the annual rate on monthly payments. - [REPLACE](https://practiceexcelonline.com/functions/replace.md): Replaces part of a text by position. From a start character, a number of characters is swapped for new text. To replace by content instead, use SUBSTITUTE. - [RIGHT](https://practiceexcelonline.com/functions/right.md): Returns the last characters of a text. The result is text, even when it looks like a number. - [ROUND](https://practiceexcelonline.com/functions/round.md): Rounds a number to a given number of decimals. Halves round away from zero; negative decimals round to tens, hundreds and so on. - [ROUNDDOWN](https://practiceexcelonline.com/functions/rounddown.md): Rounds a number down, toward zero, to a given number of decimals. - [ROUNDUP](https://practiceexcelonline.com/functions/roundup.md): Rounds a number up, away from zero, to a given number of decimals. - [ROW](https://practiceexcelonline.com/functions/row.md): Returns the row number of a cell. Left empty, it returns the row of the cell the formula is in. - [SEARCH](https://practiceexcelonline.com/functions/search.md): Returns the position of one text inside another, ignoring upper and lower case. Wildcards * and ? are allowed. - [SEQUENCE](https://practiceexcelonline.com/functions/sequence.md): Returns a list of numbers in sequence, such as 1, 2, 3, that spills into rows and columns. You choose the size, the start and the step. - [SMALL](https://practiceexcelonline.com/functions/small.md): Returns the k-th smallest value in a range: k = 1 is the minimum. - [SORT](https://practiceexcelonline.com/functions/sort.md): Sorts the rows of a range by one of its columns and spills the sorted copy into the cells below. The original data stays as it is. - [SORTBY](https://practiceexcelonline.com/functions/sortby.md): Sorts a range by the values in another range, which need not be part of the result. Add more by-range and order pairs to break ties. - [SQRT](https://practiceexcelonline.com/functions/sqrt.md): Returns the square root of a number. A negative number gives - [STDEV.P](https://practiceexcelonline.com/functions/stdev-p.md): Returns the standard deviation of a whole population, for when your data is every value there is and not a sample. - [STDEV.S](https://practiceexcelonline.com/functions/stdev-s.md): Estimates the standard deviation of a sample, which is how far the values typically sit from their average. Text and empty cells in a range are ignored. - [SUBSTITUTE](https://practiceexcelonline.com/functions/substitute.md): Replaces existing text with new text, everywhere or only the n-th time it appears. - [SUM](https://practiceexcelonline.com/functions/sum.md): Adds numbers, cell references and ranges. Text and blank cells in a range are ignored. - [SUMIF](https://practiceexcelonline.com/functions/sumif.md): Adds the cells that meet one condition. The condition can be checked in the same range or in another column. - [SUMIFS](https://practiceexcelonline.com/functions/sumifs.md): Adds the cells that meet every one of several conditions. The sum range comes first. - [SUMPRODUCT](https://practiceexcelonline.com/functions/sumproduct.md): Multiplies ranges row by row and adds the results. With a condition in parentheses it works as a flexible SUMIFS. - [SWITCH](https://practiceexcelonline.com/functions/switch.md): Compares one value against a list and returns the result for the first match, with an optional default at the end. - [TEXT](https://practiceexcelonline.com/functions/text.md): Formats a number as text with a number format code, for example to put it inside a sentence. - [TEXTAFTER](https://practiceexcelonline.com/functions/textafter.md): Returns the text after a delimiter, such as the domain after the @ in an email address. You can choose which occurrence of the delimiter to use. - [TEXTBEFORE](https://practiceexcelonline.com/functions/textbefore.md): Returns the text before a delimiter, such as everything before the @ in an email address. You can choose which occurrence of the delimiter to use. - [TEXTJOIN](https://practiceexcelonline.com/functions/textjoin.md): Joins texts with a delimiter between them, and can skip empty cells. - [TEXTSPLIT](https://practiceexcelonline.com/functions/textsplit.md): Splits text into pieces at a delimiter and spills them across columns, or down rows when you also give a row delimiter. - [TIME](https://practiceexcelonline.com/functions/time.md): Builds a time from an hour, a minute and a second. The result is a fraction of a day, so TIME(12,0,0) is 0.5. - [TODAY](https://practiceexcelonline.com/functions/today.md): Returns today's date. It updates every time the sheet recalculates. - [TRANSPOSE](https://practiceexcelonline.com/functions/transpose.md): Turns rows into columns and columns into rows. The result spills and stays linked to the original cells. - [TRIM](https://practiceexcelonline.com/functions/trim.md): Removes spaces from the start and end of a text and turns runs of spaces inside it into one. - [TRUNC](https://practiceexcelonline.com/functions/trunc.md): Cuts off the decimals of a number without rounding. TRUNC(8.9) is 8, and TRUNC(-8.9) is -8. - [UNIQUE](https://practiceexcelonline.com/functions/unique.md): Returns the distinct values of a range as a list that spills into the cells below. - [UPPER](https://practiceexcelonline.com/functions/upper.md): Converts text to capital letters. - [VALUE](https://practiceexcelonline.com/functions/value.md): Converts text that looks like a number, such as "1,250" or "15%", into a real number you can calculate with. - [VLOOKUP](https://practiceexcelonline.com/functions/vlookup.md): Looks for a value in the first column of a table and returns the value from another column in the same row. - [WEEKDAY](https://practiceexcelonline.com/functions/weekday.md): Returns the day of the week of a date as a number. By default 1 is Sunday and 7 is Saturday; with 2 as the second argument, Monday is 1. - [WEEKNUM](https://practiceexcelonline.com/functions/weeknum.md): Returns the week number of a date. By default weeks start on Sunday and week 1 is the week with Jan 1 in it; return type 21 gives ISO week numbers, as used in Europe. - [WORKDAY](https://practiceexcelonline.com/functions/workday.md): Returns the date a number of working days before or after a start date, skipping weekends and any holidays you list. - [XLOOKUP](https://practiceexcelonline.com/functions/xlookup.md): Finds a value in one range and returns the matching item from another. Exact match by default, with an optional value for not found. - [XMATCH](https://practiceexcelonline.com/functions/xmatch.md): Returns the position of a value in a range, like MATCH but with exact match as the default and search from the end. - [YEAR](https://practiceexcelonline.com/functions/year.md): Returns the year of a date as a number. ## Excel keyboard shortcuts - [Excel shortcut speed-run](https://practiceexcelonline.com/shortcuts/speed-run.md) - [Accept a suggested function](https://practiceexcelonline.com/shortcuts/accept-autocomplete.md): Windows Tab, Mac Tab - [Add or edit a note](https://practiceexcelonline.com/shortcuts/add-note.md): Windows Shift+F2, Mac Fn+Shift+F2 - [Add another range to the selection](https://practiceexcelonline.com/shortcuts/add-to-selection.md): Windows Shift+F8, Mac Fn+Shift+F8 - [Enter an array formula](https://practiceexcelonline.com/shortcuts/array-formula.md): Windows Ctrl+Shift+Enter, Mac Ctrl+Shift+Return - [Insert AutoSum](https://practiceexcelonline.com/shortcuts/autosum.md): Windows Alt+=, Mac Cmd+Shift+T - [Recalculate all open workbooks](https://practiceexcelonline.com/shortcuts/calculate-now.md): Windows F9, Mac Fn+F9 - [Recalculate the active sheet](https://practiceexcelonline.com/shortcuts/calculate-sheet.md): Windows Shift+F9, Mac Fn+Shift+F9 - [Cancel the entry](https://practiceexcelonline.com/shortcuts/cancel-entry.md): Windows Esc, Mac Esc - [Create a chart on a new sheet](https://practiceexcelonline.com/shortcuts/chart-sheet.md): Windows F11, Mac Fn+F11 - [Clear the cell contents](https://practiceexcelonline.com/shortcuts/clear-contents.md): Windows Delete, Mac Delete - [Close Excel](https://practiceexcelonline.com/shortcuts/close-excel.md): Windows Alt+F4, Mac Cmd+Q - [Close the workbook](https://practiceexcelonline.com/shortcuts/close-workbook.md): Windows Ctrl+W, Mac Cmd+W - [Open the right-click menu](https://practiceexcelonline.com/shortcuts/context-menu.md): Windows Shift+F10, Mac Fn+Shift+F10 - [Copy](https://practiceexcelonline.com/shortcuts/copy.md): Windows Ctrl+C, Mac Cmd+C - [Format data as a table](https://practiceexcelonline.com/shortcuts/create-table.md): Windows Ctrl+T, Mac Ctrl+T - [Apply the Currency format](https://practiceexcelonline.com/shortcuts/currency-format.md): Windows Ctrl+Shift+4, Mac Ctrl+Shift+4 - [Cut](https://practiceexcelonline.com/shortcuts/cut.md): Windows Ctrl+X, Mac Cmd+X - [Apply the Date format](https://practiceexcelonline.com/shortcuts/date-format.md): Windows Ctrl+Shift+3, Mac Ctrl+Shift+3 - [Delete the selected rows or cells](https://practiceexcelonline.com/shortcuts/delete-row.md): Windows Ctrl+-, Mac Cmd+- - [Edit the active cell](https://practiceexcelonline.com/shortcuts/edit-cell.md): Windows F2, Mac Ctrl+U - [Confirm and move down](https://practiceexcelonline.com/shortcuts/enter-move-down.md): Windows Enter, Mac Return - [Turn extend selection mode on or off](https://practiceexcelonline.com/shortcuts/extend-mode.md): Windows F8, Mac Fn+F8 - [Extend the selection by one cell](https://practiceexcelonline.com/shortcuts/extend-selection.md): Windows Shift+Right, Mac Shift+Right - [Fill down](https://practiceexcelonline.com/shortcuts/fill-down.md): Windows Ctrl+D, Mac Cmd+D - [Fill right](https://practiceexcelonline.com/shortcuts/fill-right.md): Windows Ctrl+R, Mac Cmd+R - [Enter the same value in every selected cell](https://practiceexcelonline.com/shortcuts/fill-selection.md): Windows Ctrl+Enter, Mac Ctrl+Return - [Open Format Cells](https://practiceexcelonline.com/shortcuts/format-cells.md): Windows Ctrl+1, Mac Cmd+1 - [Open Go To](https://practiceexcelonline.com/shortcuts/go-to.md): Windows F5, Mac Ctrl+G - [Go to cell A1](https://practiceexcelonline.com/shortcuts/go-to-a1.md): Windows Ctrl+Home, Mac Fn+Ctrl+Left - [Go to the last used cell](https://practiceexcelonline.com/shortcuts/go-to-last-cell.md): Windows Ctrl+End, Mac Fn+Ctrl+Right - [Go to the start of the row](https://practiceexcelonline.com/shortcuts/go-to-row-start.md): Windows Home, Mac Fn+Left - [Group rows or columns](https://practiceexcelonline.com/shortcuts/group-rows.md): Windows Alt+Shift+Right, Mac Cmd+Shift+K - [Hide the selected columns](https://practiceexcelonline.com/shortcuts/hide-column.md): Windows Ctrl+0, Mac Ctrl+0 - [Hide the selected rows](https://practiceexcelonline.com/shortcuts/hide-row.md): Windows Ctrl+9, Mac Ctrl+9 - [Insert a chart on the sheet](https://practiceexcelonline.com/shortcuts/insert-chart.md): Windows Alt+F1, Mac Fn+Option+F1 - [Open Insert Function](https://practiceexcelonline.com/shortcuts/insert-function.md): Windows Shift+F3, Mac Shift+F3 - [Jump to the last filled cell in a column](https://practiceexcelonline.com/shortcuts/jump-to-edge.md): Windows Ctrl+Down, Mac Cmd+Down - [Open the list of macros](https://practiceexcelonline.com/shortcuts/macros.md): Windows Alt+F8, Mac Fn+Option+F8 - [Confirm and move left](https://practiceexcelonline.com/shortcuts/move-left.md): Windows Shift+Tab, Mac Shift+Tab - [Confirm and move up](https://practiceexcelonline.com/shortcuts/move-up.md): Windows Shift+Enter, Mac Shift+Return - [Start a new line in a cell](https://practiceexcelonline.com/shortcuts/new-line-in-cell.md): Windows Alt+Enter, Mac Option+Return - [Insert a new sheet](https://practiceexcelonline.com/shortcuts/new-sheet.md): Windows Shift+F11, Mac Shift+F11 - [Create a new workbook](https://practiceexcelonline.com/shortcuts/new-workbook.md): Windows Ctrl+N, Mac Cmd+N - [Move to the next sheet](https://practiceexcelonline.com/shortcuts/next-sheet.md): Windows Ctrl+PageDown, Mac Option+Right - [Switch to the next open workbook](https://practiceexcelonline.com/shortcuts/next-workbook.md): Windows Ctrl+Tab, Mac Ctrl+Tab - [Apply the Number format](https://practiceexcelonline.com/shortcuts/number-format.md): Windows Ctrl+Shift+1, Mac Ctrl+Shift+1 - [Open a dropdown list](https://practiceexcelonline.com/shortcuts/open-dropdown.md): Windows Alt+Down, Mac Option+Down - [Open the VBA editor](https://practiceexcelonline.com/shortcuts/open-vba.md): Windows Alt+F11, Mac Fn+Option+F11 - [Add an outline border](https://practiceexcelonline.com/shortcuts/outline-border.md): Windows Ctrl+Shift+7, Mac Cmd+Option+0 - [Move one screen down](https://practiceexcelonline.com/shortcuts/page-down.md): Windows PageDown, Mac Fn+Down - [Move one screen to the right](https://practiceexcelonline.com/shortcuts/page-right.md): Windows Alt+PageDown, Mac Fn+Option+Down - [Move one screen up](https://practiceexcelonline.com/shortcuts/page-up.md): Windows PageUp, Mac Fn+Up - [Paste](https://practiceexcelonline.com/shortcuts/paste.md): Windows Ctrl+V, Mac Cmd+V - [Paste Special](https://practiceexcelonline.com/shortcuts/paste-special.md): Windows Ctrl+Alt+V, Mac Ctrl+Cmd+V - [Apply the percent format](https://practiceexcelonline.com/shortcuts/percent-format.md): Windows Ctrl+Shift+5, Mac Ctrl+Shift+5 - [Move to the previous sheet](https://practiceexcelonline.com/shortcuts/previous-sheet.md): Windows Ctrl+PageUp, Mac Option+Left - [Redo](https://practiceexcelonline.com/shortcuts/redo.md): Windows Ctrl+Y, Mac Cmd+Y - [Repeat the last action](https://practiceexcelonline.com/shortcuts/repeat-action.md): Windows F4, Mac Cmd+Y - [Save the workbook](https://practiceexcelonline.com/shortcuts/save.md): Windows Ctrl+S, Mac Cmd+S - [Save as a new file](https://practiceexcelonline.com/shortcuts/save-as.md): Windows F12, Mac Cmd+Shift+S - [Apply the Scientific format](https://practiceexcelonline.com/shortcuts/scientific-format.md): Windows Ctrl+Shift+6, Mac Ctrl+Shift+6 - [Select the current region or the whole sheet](https://practiceexcelonline.com/shortcuts/select-all.md): Windows Ctrl+A, Mac Cmd+A - [Select the whole column](https://practiceexcelonline.com/shortcuts/select-column.md): Windows Ctrl+Space, Mac Ctrl+Space - [Select one screen down](https://practiceexcelonline.com/shortcuts/select-page-down.md): Windows Shift+PageDown, Mac Fn+Shift+Down - [Select the whole row](https://practiceexcelonline.com/shortcuts/select-row.md): Windows Shift+Space, Mac Shift+Space - [Select to the start of the sheet](https://practiceexcelonline.com/shortcuts/select-to-a1.md): Windows Ctrl+Shift+Home, Mac Fn+Ctrl+Shift+Left - [Select to the last filled cell](https://practiceexcelonline.com/shortcuts/select-to-edge.md): Windows Ctrl+Shift+Down, Mac Cmd+Shift+Down - [Select to the last cell in use](https://practiceexcelonline.com/shortcuts/select-to-last-cell.md): Windows Ctrl+Shift+End, Mac Fn+Ctrl+Shift+Right - [Select to the start of the row](https://practiceexcelonline.com/shortcuts/select-to-row-start.md): Windows Shift+Home, Mac Fn+Shift+Left - [Scroll back to the active cell](https://practiceexcelonline.com/shortcuts/show-active-cell.md): Windows Ctrl+Backspace, Mac Ctrl+Delete - [Check spelling](https://practiceexcelonline.com/shortcuts/spell-check.md): Windows F7, Mac Fn+F7 - [Apply strikethrough](https://practiceexcelonline.com/shortcuts/strikethrough.md): Windows Ctrl+5, Mac Cmd+Shift+X - [Confirm and move right](https://practiceexcelonline.com/shortcuts/tab-move-right.md): Windows Tab, Mac Tab - [Apply the Time format](https://practiceexcelonline.com/shortcuts/time-format.md): Windows Ctrl+Shift+2, Mac Ctrl+Shift+2 - [Toggle absolute and relative references](https://practiceexcelonline.com/shortcuts/toggle-absolute.md): Windows F4, Mac Cmd+T - [Turn filters on or off](https://practiceexcelonline.com/shortcuts/toggle-filter.md): Windows Ctrl+Shift+L, Mac Cmd+Shift+F - [Show or hide the ribbon](https://practiceexcelonline.com/shortcuts/toggle-ribbon.md): Windows Ctrl+F1, Mac Cmd+Option+R - [Undo](https://practiceexcelonline.com/shortcuts/undo.md): Windows Ctrl+Z, Mac Cmd+Z - [Ungroup rows or columns](https://practiceexcelonline.com/shortcuts/ungroup-rows.md): Windows Alt+Shift+Left, Mac Cmd+Shift+J - [Unhide rows](https://practiceexcelonline.com/shortcuts/unhide-rows.md): Windows Ctrl+Shift+9, Mac Ctrl+Shift+9 ## Excel tests for job applications - [Indeed Excel assessment practice test](https://practiceexcelonline.com/tests/indeed-excel-assessment.md): 20 questions in 30 minutes; the first 10 are a free preview. - [Excel test for job interviews](https://practiceexcelonline.com/tests/excel-test-for-job-interview.md): 20 questions in 30 minutes; the first 10 are a free preview. - [Kenexa Excel test practice](https://practiceexcelonline.com/tests/kenexa-excel-test.md): 20 questions in 30 minutes; the first 10 are a free preview. - [SHL Excel test practice](https://practiceexcelonline.com/tests/shl-excel-test.md): 20 questions in 25 minutes; the first 10 are a free preview. - [iMocha Excel test practice](https://practiceexcelonline.com/tests/imocha-excel-test.md): 20 questions in 25 minutes; the first 10 are a free preview. - [TestGorilla Excel test practice](https://practiceexcelonline.com/tests/testgorilla-excel-test.md): 20 questions in 20 minutes; the first 10 are a free preview. - [Excel test for finance jobs](https://practiceexcelonline.com/tests/excel-test-for-finance.md): 20 questions in 30 minutes; the first 10 are a free preview. - [Excel test for accounting and bookkeeping](https://practiceexcelonline.com/tests/excel-test-for-accountants.md): 20 questions in 30 minutes; the first 10 are a free preview. - [Excel test for budgeting and planning](https://practiceexcelonline.com/tests/excel-test-for-budgeting.md): 20 questions in 30 minutes; the first 10 are a free preview. - [Excel test for admin jobs](https://practiceexcelonline.com/tests/excel-test-for-admin-jobs.md): 20 questions in 30 minutes; the first 10 are a free preview. - [Excel test for data analysts](https://practiceexcelonline.com/tests/excel-test-for-data-analysts.md): 20 questions in 30 minutes; the first 10 are a free preview. ## Excel guides - [Excel for job interviews: what gets tested and how to prepare](https://practiceexcelonline.com/learn/excel-for-job-interviews.md): What employer Excel tests ask, the formulas that come up again and again, how the timer changes your approach, and a two-week plan to get ready. - [How to learn Excel: a practice plan from your first formula to job-ready](https://practiceexcelonline.com/learn/how-to-learn-excel.md): A practical order for learning Excel, from cells and SUM to lookups and summary tables, with how long each stage takes and how to practice so it sticks. - [VLOOKUP vs XLOOKUP vs INDEX MATCH: which lookup to use](https://practiceexcelonline.com/learn/vlookup-vs-xlookup-vs-index-match.md): How VLOOKUP, XLOOKUP and INDEX with MATCH work, where each one breaks, and which to use in a test, an old workbook or a sheet you are building today. - [Pivot tables explained, and the formulas that do the same job](https://practiceexcelonline.com/learn/pivot-tables.md): What a pivot table computes, how to build one step by step, the problems that trip people up, and how to get the same summaries with SUMIF, COUNTIF and SUMIFS. - [Excel for finance and accounting: the skills you actually use](https://practiceexcelonline.com/learn/excel-for-finance.md): The Excel skills finance and accounting teams use every day, from SUMIFS on a ledger and budget variances to rounding, month-end dates, PMT, NPV and check totals. - [Excel IF formulas: from one condition to many](https://practiceexcelonline.com/learn/excel-if-function.md): How IF works, how to combine conditions with AND and OR, when to nest IFs or switch to IFS, and how to fix the IF mistakes that give wrong answers without an error. - [Dates in Excel: how they work and the formulas you need](https://practiceexcelonline.com/learn/excel-dates.md): Why Excel stores dates as numbers, how to add days and months, find month-ends and working days, work out ages, and fix dates that arrive as text. - [How to clean data in Excel with formulas](https://practiceexcelonline.com/learn/clean-data-in-excel.md): Fix messy exports with formulas. Remove extra spaces, fix capitals, split names and codes, join columns, find duplicates and turn text into real numbers. - [Excel keyboard shortcuts worth learning, and how to make them stick](https://practiceexcelonline.com/learn/excel-keyboard-shortcuts.md): The Excel shortcuts that save real time, for Windows and Mac, in the order to learn them, and a short daily drill that makes them automatic. - [Google Sheets vs Excel: what is the same, what is different, and how to switch](https://practiceexcelonline.com/learn/google-sheets-vs-excel.md): Where Google Sheets and Excel work the same, where they differ, from QUERY and ARRAYFORMULA to sharing and limits, and what to learn when you switch. ## Google Sheets practice exercises - [Google Sheets practice exercises](https://practiceexcelonline.com/sheets.md) - [QUERY](https://practiceexcelonline.com/sheets/practice/query.md): 5 lessons - [ARRAYFORMULA](https://practiceexcelonline.com/sheets/practice/arrayformula.md): 3 lessons - [SPLIT and JOIN](https://practiceexcelonline.com/sheets/practice/split-join.md): 3 lessons - [REGEX functions](https://practiceexcelonline.com/sheets/practice/regex.md): 3 lessons - [IF](https://practiceexcelonline.com/sheets/practice/if.md): 5 lessons - [COUNTIF](https://practiceexcelonline.com/sheets/practice/countif.md): 5 lessons - [SUMIF](https://practiceexcelonline.com/sheets/practice/sumif.md): 5 lessons - [SUMIFS](https://practiceexcelonline.com/sheets/practice/sumifs.md): 4 lessons - [COUNTIFS](https://practiceexcelonline.com/sheets/practice/countifs.md): 4 lessons - [VLOOKUP](https://practiceexcelonline.com/sheets/practice/vlookup.md): 4 lessons - [INDEX and MATCH](https://practiceexcelonline.com/sheets/practice/index-match.md): 4 lessons - [XLOOKUP](https://practiceexcelonline.com/sheets/practice/xlookup.md): 4 lessons - [IFERROR](https://practiceexcelonline.com/sheets/practice/iferror.md): 3 lessons ## Optional - [Excel practice exercises](https://practiceexcelonline.com/practice.md) - [Excel functions](https://practiceexcelonline.com/functions.md) - [Excel keyboard shortcuts](https://practiceexcelonline.com/shortcuts.md) - [Excel tests for job applications](https://practiceexcelonline.com/tests.md) - [Excel guides](https://practiceexcelonline.com/learn.md) - [Pricing](https://practiceexcelonline.com/pricing.md) - [XML sitemap index](https://practiceexcelonline.com/sitemap_index.xml)