123 useful answers · Page 1 of 6
Work through formulas, formatting, sorting, and everyday data tasks.
Search this topicIn Excel, select View, then Freeze Panes, then Freeze Top Row to keep row 1 visible. To hold several rows and a left-hand label column, select the cell immediately below and to the right of that area before choosing Freeze Panes. For example, selecting B3 keeps rows 1–2 and column A visible. Use Unfreeze Panes to reset the view.
A reference such as B2 moves relative to the formula when copied. Add dollar signs to fix a reference: $B$2 always points to that cell. For prices in A2:A10 and one tax rate in B1, enter =A2*$B$1 and copy down. The price row changes; the tax cell stays fixed. Check the first and last copied formulas before trusting the results.
Click an empty cell below your numbers and enter =SUM(B2:B20), replacing the range with yours. Keep the total cell outside the range so it does not include itself. For two separate blocks, use =SUM(B2:B10,D2:D10). Test with a few numbers you can add mentally, then confirm that the range includes the final data row and excludes any existing subtotals.
Select the entire table, including every column that belongs to each record. In Google Sheets, choose Data, Sort range, then Advanced range sorting options. Mark Data has header row and select the column to sort. For a contact list, sort the full name-and-email table, not just the names. Check a known person's details afterward to confirm the record stayed together.
Select your table and choose Data, then Create a filter. Open the filter control in the relevant header and select the values you want, such as only Unpaid invoices. Filtering hides other rows without deleting them. Clear the filter to see everything again. In a shared file, remember that a regular filter is visible to collaborators; use a filter view for a separate view.
Use SORT in a separate empty area. In Google Sheets, =SORT(A2:C20,2,TRUE) returns all three columns ordered by the second column, ascending. Replace TRUE with FALSE for descending order. Leave enough empty rows and columns for the results, and keep the source range separate from the formula. This is useful when an entry sheet needs its original order preserved.
In Google Sheets on a computer, select the status cells and choose Insert, then Dropdown. Enter options such as Not started, Doing and Done, then save the rule. Use Dropdown from a range if another part of the sheet stores your choices. Check the invalid-data setting: reject other values for consistent entries, or show a warning when exceptions should be allowed.
In Excel, select the cells to inspect, then choose Home, Conditional Formatting, Highlight Cells Rules and Duplicate Values. Review the highlighted cells in context: two customers can share a surname without being the same person. If removal is appropriate, copy the original table first. Under Data, Remove Duplicates, choose the columns that together define a duplicate record.
In Google Sheets, leave empty columns beside the data, select the cells and choose Data, then Split text to columns. Set the separator to comma or the character your data actually uses. Try a few rows first. A value such as Boston, MA, USA needs three columns; names or addresses containing their own commas may need a more careful import approach.
Select the text cells in Google Sheets and use Data, Data cleanup, Trim whitespace. Inspect a few cleaned values before continuing with matching or counting formulas. This removes leading, trailing and repeated spaces, but does not remove non-breaking spaces. If a copied web value still fails to match, compare it with a freshly typed version before applying more cleanup to the whole column.
Use COUNTIF when one condition determines which cells count. In Google Sheets, =COUNTIF(C2:C50,"Done") counts matching status cells, while =COUNTIF(B2:B50,">100") counts numbers above 100. Put text and comparison operators inside quotation marks. Check that your range covers the intended records. If each row must satisfy two different conditions, switch to COUNTIFS rather than adding two separate counts.
Select the numeric cells in Excel, then choose Home, Conditional Formatting, Highlight Cells Rules and Greater Than. Enter your threshold and choose a readable format. For example, highlight expenses above 75 in a budget column. Test values below, equal to and above the limit. Greater Than excludes the exact threshold; use a different rule if the limit itself should count.
Use SUMIF when a label decides which amounts belong in the total. If A2:A20 contains categories and B2:B20 contains amounts, =SUMIF(A2:A20,"Supplies",B2:B20) adds only Supplies rows. Put the category in D2 and use D2 instead of quoted text to make the summary reusable. Keep the category and amount ranges aligned, and check the total against a manually filtered sample.
For numbers in B2:B20, =SUMIF(B2:B20,">100") adds values strictly above 100. To use a threshold stored in D2, write =SUMIF(B2:B20,">"&D2). Change > to >= if the boundary should count. With 80, 100 and 120, the first formula totals 120; the inclusive version totals 220. Test that boundary before applying the formula to a long list.
With regions in A, statuses in B and amounts in C, use =SUMIFS(C2:C50,A2:A50,"East",B2:B50,"Paid"). SUMIFS puts the amount range first, unlike SUMIF. A row contributes only when both conditions match. Use ranges with the same first and last rows. Check one East/Paid row and one excluded row before copying the formula into a report.
Store actual dates in A and amounts in B. To total January 2026, use =SUMIFS(B2:B100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1)). The exclusive next-month boundary also handles timestamps on the final day. Confirm that dates are numeric Excel dates rather than imported text. Do not filter by a month name alone if the data spans several years.
Use =COUNTIFS(A2:A50,"East",B2:B50,"Open") to count rows whose region is East and status is Open. COUNTIFS applies every condition to the same row. Keep its ranges equal in size. It counts matching records, not distinct customers: if one customer has three qualifying records, the result includes three. Decide whether you need record counts or unique people before using the result.
For values from 10 through 20, including both endpoints, enter =COUNTIFS(A2:A50,">=10",A2:A50,"<=20"). For limits in D2 and E2, use =COUNTIFS(A2:A50,">="&D2,A2:A50,"<="&E2). Test 9, 10, 20 and 21: only the middle two should count. Using the same range twice is intentional because both conditions describe each value.
Enter =COUNTIF(A2:A50,"*urgent*") to count cells containing urgent anywhere in their text. The asterisks match any surrounding characters. This also matches longer strings containing those letters, and it is not case-sensitive or a whole-word search. If you need an exact status label, remove the asterisks. Test a few near-matches before using the count to classify records.
COUNTIF normally treats * as a wildcard. Put a tilde before it to make it literal: =COUNTIF(A2:A50,"*~**") counts cells containing an asterisk character. For a literal question mark, use "*~?*"; for a literal tilde, use "*~~*". Include sample cells with and without the symbol to verify that the formula is counting punctuation rather than every text entry.
Use =COUNT(A2:A50) to count cells containing numeric values. Text labels and empty cells are ignored, but dates and times count because Excel stores them as numbers. A number imported as text may not count until converted. Compare COUNT with the expected number of numeric records before averaging a column; a plausible-looking total can hide missing text-formatted numbers.
Use =COUNTA(A2:A50) to count nonempty cells in a range. It includes text, numbers, errors and formulas returning an empty string. That last case matters: a cell that looks blank can still count. Use COUNTA for occupied entries, but inspect formula-generated lists before treating the result as a count of visible names or completed tasks.
Enter =COUNTBLANK(A2:A50) for the data rows you want to inspect. It counts empty cells and formulas that return an empty string, but it does not count numeric zero as blank. Avoid including hundreds of unused rows below your table. If a supposedly blank cell is not counted, check whether it contains a space or another invisible character.
Use =AVERAGEIF(B2:B50,"<>0") to average nonzero numeric values. Only exclude zero when it represents something you intentionally want to omit; a genuine zero score or zero sale belongs in many averages. If no values qualify, the formula returns a division error. Try 0, 10 and 20: the nonzero average is 15, while the ordinary average is 10.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.