How to Create and Format an Excel Spreadsheet (Gift List Example)

Create an Excel spreadsheet by opening a blank workbook, adding headings and records, formatting the cells, and saving the file. This guide uses a Christmas gift list as a practical example, then shows how to total and average its prices. You can use the same structure for other lists and budgets.

This guide uses features from Microsoft’s Excel tables, Total Row, and checkbox help articles, checked in October 2026. It works in Excel for Microsoft 365 on Windows and Mac; menu names can differ slightly in older versions.

Create and Save a New Excel Workbook

A workbook is the Excel file; worksheets are the tabs inside it. The gift list below is a worked example, but these same basics apply to a household budget or another simple list.

1. Choose File > New > Blank workbook in Excel for Windows, or File > New Workbook on Mac.
2. Click A1, type your first column heading, and press Tab for the next column. Start the actual records in row 2, keeping one record per row.
3. Enter numbers as numbers, then apply a number format. For a total outside your data, select an empty cell and enter a formula such as =SUM(C2:C4).
4. Choose File > Save As for a new local file, select a folder and name, and save as an Excel Workbook (.xlsx). Use Ctrl+S on Windows or Command+S on Mac for later saves.

Reopen the saved file once to check its location and contents. Keep the .xlsx format when you need multiple sheets, formulas, and formatting. Microsoft’s basic Excel tasks explains workbook creation, data entry, and saving.

Create, Format, Resize or Remove an Excel Table

For a general-purpose Excel table, select your records and their heading row, choose Insert > Table, check the proposed range and select My table has headers when the first row contains labels, then choose OK. Alternatively, choose Home > Format as Table and pick a style before confirming the range. Microsoft’s table-creation guide describes Windows, Mac and web controls. Confirm the table covers the intended records and has its own header/filter controls; headings typed in ordinary worksheet cells alone do not make a structured table.

Click inside the table to reveal Table Design (Table Tools > Design in Excel 2013; Table on Mac). Choose a Table Style and use its style options for a Header Row, Total Row, Banded Rows or Banded Columns; the latter alternates columns, not rows. First/Last Column options emphasize those columns. To sort or filter, open a header’s dropdown, choose a sort order or select the values to show, then apply the filter. Keep complete records together and clear the filter when you need every row visible. Microsoft’s table-formatting guide covers these design controls; the Total Row section below explains totals for visible records.

In Windows Excel, click inside the table and choose Table Design > Resize Table. Select the new range, including the existing header row, and choose OK; Microsoft’s resize instructions require the new range to overlap the original. Check all intended records are included. To stop using table features without deleting the data, right-click a table cell and choose Table > Convert to Range, then confirm Yes if prompted. On Mac, use Table > Convert to Range. Microsoft’s conversion guide explains that table-specific filters and structured references change, while the data and applied style remain. Review formulas and formatting after conversion.

Create and Check a PivotTable

Arrange the source data in columns with one header row, no blank separator rows and consistent values. Select the complete range including its headers, or a cell in your Excel table. Choose Insert > PivotTable, check the source table/range, choose New Worksheet or an empty location on an Existing Worksheet, then select OK. In the PivotTable Fields pane, drag a category such as Recipient into Rows, another category into Columns when useful, and a numeric field such as Cost into Values. Drag an optional field into Filters to filter the report. The desktop Excel 2010/2013 ribbon also uses Insert > PivotTable; older versions can label the contextual ribbon differently. Microsoft’s creation guide explains the range, destination and field areas.

Check a known subtotal against the original rows. If Values shows Count of Cost when you need a total, use that field’s dropdown > Value Field Settings > Sum > OK, and check that costs are numbers rather than text. To count records, choose Count deliberately. After editing or adding source rows, right-click a PivotTable cell and choose Refresh; for a fixed range, use the contextual PivotTable ribbon’s Change Data Source control to include any added rows first. An Excel table can include added rows when refreshed. Refresh availability varies by Excel version, so do not assume every report updates automatically. Save and reopen the workbook to check both source and report. Removing a PivotTable does not require deleting the original data.

How to Make a Christmas List Spreadsheet in Excel

1. Open a blank workbook in Excel. Result: an empty sheet appears.
2. In row 1, type your headings. For example: Name in A1, Gift Idea in B1, Price in C1, Bought in D1, and Wrapped in E1. Result: your columns are labeled.
3. Starting in row 2, add each person and their gift, one per row. Result: your list takes shape.
4. Select all of your data, including the headings. Result: the range is highlighted.
5. Go to Home > Format as Table and pick a style. Result: a box asks about headers.
6. Check My table has headers and click OK. Result: your list becomes a table with filter arrows on each heading.

The filter arrows let you sort by price or show only the gifts you still need to buy. New rows you add at the bottom join the table automatically. Need ideas for the Gift column? See our list of small tech gifts that people use every day.

Format Cells in Excel

For this gift list, format the Price cells as currency and wrap long gift descriptions. In desktop Excel, select the cells, right-click and choose Format Cells, or press Ctrl+1 on Windows / Command+1 on Mac.

• Number: choose Currency, Number, Percentage, Date, or another display format. For prices, choose Currency and two decimal places.
• Alignment: adjust placement and turn on Wrap Text for long descriptions.
• Font: change text size, style, or color.
• Border: choose the lines around the selected cells.
• Fill: choose a background color; older documentation may call this Patterns.
• Protection: set Locked or Hidden attributes. These only take effect after you protect the worksheet.

Click OK and check the selected cells. A number format changes the display, not the stored number. See Microsoft’s Format Cells settings and worksheet protection guidance.

To remove direct formatting while keeping the data, select the cells and choose Home > Clear > Clear Formats. Do not choose Clear All, which also clears data. See Microsoft’s clearing options. Excel for the web exposes formatting through the ribbon and may not show the same full desktop dialog.

Choose How Excel Displays Negative Numbers

Select the numeric cells and press Ctrl+1 in Windows Excel, or Command+1 on Mac. On the Number tab, choose Number or Currency, set the desired decimal places and select an offered style under Negative numbers, such as a minus sign, red text or parentheses. Inspect the Sample, select OK and check a known negative value such as -25. Microsoft’s negative-number guide notes that available parentheses choices can depend on operating-system settings. This also describes the Format Cells approach used in the older Excel tutorial. Formatting changes appearance, not the value’s sign or arithmetic. If an imported entry is text, fix that separately; choose your previous format or Undo to reverse the display change.

Wrap Text So Long Descriptions Fit

1. Select the cells containing the long descriptions.
2. On the Home tab, select Wrap Text in the Alignment group.
3. If words are still cut off, select Home > Format > AutoFit Row Height, or drag the bottom border of the row heading down.

Wrapping displays the same cell’s text on several lines. It does not split the value into several cells. Narrowing the column makes more lines; widening it makes fewer. To put a break at a specific place in Windows Excel, edit the cell, place the cursor there, and press Alt+Enter. To undo wrapping, select Wrap Text again.

Merged cells and a fixed row height can keep some text hidden. Adjust the height manually if AutoFit cannot show all the text. Microsoft’s Wrap text in a cell guide explains these limits. On Mac, Home > Wrap Text works too; use Format > Row > AutoFit if needed.

Change How Dates Display in Excel

1. Select the cells that contain the dates.
2. In desktop Excel, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac.
3. On the Number tab, choose Date, select a format under Type, check the Sample, and select OK.

For a year-month-day display, choose Custom and enter yyyy-mm-dd. In Excel for the web, use Home > Number Format > Short Date or Long Date. See Microsoft’s date-format guide.

Formatting changes the appearance of a real date, not the date’s value. It does not convert imported text into dates. If the result is #####, widen the column. Confirm that day and month are interpreted correctly before sorting or calculating with imported dates.

How to Add Up Your Christmas Spending

1. Click anywhere in the table. Result: the Table Design tab appears.
2. On the Table Design tab (called Table Tools > Design in some versions), check Total Row. Result: a total row appears at the bottom.
3. Click the cell under Price in the total row and choose Sum from the drop-down. Result: Excel shows your total spending.

Microsoft notes the total row uses the SUBTOTAL function, so if you filter the list, the total only counts the rows you can see. That makes it easy to check, for example, the total for gifts you have not bought yet.

Calculate a Total With SUM

For a total independent of the table’s Total Row, click an empty result cell outside the input range, type =SUM(, then drag across the numeric cells to include. Type ) and press Enter. For example, enter 20, 30 and 40 in C2:C4 and use =SUM(C2:C4) in G2; the answer is 90. To include another separate range, separate the arguments with a comma, such as =SUM(C2:C4,E2:E4), using your Excel locale’s separator where different. Microsoft’s SUM instructions explain selecting ranges and closing the formula. Review the formula’s ranges and change one test input to check recalculation. SUM ignores text within referenced cells and includes filtered-out numeric rows; the table’s SUBTOTAL method above is the alternative for visible rows. Avoid including a previous subtotal or the result cell itself.

Calculate an Average in Excel

To find the average gift price, put the result outside the input range:

1. Enter 20, 30, and 40 in cells C2, C3, and C4.
2. Click an empty cell such as G2, type =AVERAGE(C2:C4), and press Enter.
3. Check the answer: 30. Replace C2:C4 with the range containing your own prices.

AVERAGE ignores blank cells and text within the referenced range, but includes numeric zero. If C4 is blank, the example averages 20 and 30 to give 25; if C4 contains 0, the average is about 16.67. An error value in the range can produce an error result. Keep the formula out of its own input range to avoid a circular reference. See Microsoft’s AVERAGE function reference.

Track What You Have Bought With Checkboxes

1. Select the cells in the Bought column (and Wrapped, if you like). Result: the cells are highlighted.
2. Select Insert > Checkbox. Result: each cell gets a checkbox.
3. Click a checkbox, or select it and press the Spacebar, to check it off. Result: the box shows a check mark.

Microsoft explains that a checked box has the value TRUE and an unchecked one is FALSE. That means you can total only the gifts you have bought with a formula such as =SUMIFS(C:C, D:D, TRUE), where C is Price and D is Bought.

Do not see Checkbox on the Insert tab? It is a newer feature. Instead, make a drop-down list: select the Bought cells, go to Data > Data Validation, choose List under Allow, and enter Yes,No as the source.

Tips for a Better Gift List

• Show prices as money: select the Price column and choose Home > Accounting Number Format.
• Add a budget: add a Budget column and a Left column with a formula like =[@Budget]-[@Price]. In a table, Excel fills it down for every row.
• Color-code gifts: use cell colors to mark gifts that are bought or wrapped. See how to fill a cell with color in Excel.
• Print it on one page: see how to fit a spreadsheet on one page.
• Add a Store or Link column: note where you plan to buy each gift so you can shop faster.

Calculate an Average With AutoSum

In desktop Excel, including Excel 2013, select an empty cell directly below a column of numbers or to the right of a row. On Home, open the arrow beside AutoSum and choose Average. Check the highlighted input range before pressing Enter; adjust it if Excel guessed the wrong cells, and keep the result cell outside that range. For the gift-list amounts in C2:C4, the formula should read =AVERAGE(C2:C4) and return 30 when the entries are 20, 30 and 40. Microsoft’s average guide describes the menu method. Use the direct AVERAGE formula above when the source cells are not adjacent.

Create and Apply a Custom Excel Table Style

In desktop Excel, first create the table using the Insert Table or Home Format as Table procedure above. Select a cell inside it, open Table Design, expand the Table Styles gallery, and choose New Table Style. Name the style, select a table element, and use Format to set its font, border or fill. Repeat for the elements you want and review the preview before confirming the dialogs. Then choose the new style in the gallery’s Custom area to apply it; creating the style alone does not apply it to the selected table. Custom styles are stored in the current workbook. Save the workbook and check the table’s appearance. Microsoft’s table-formatting guide explains these controls. Clear in the style gallery removes the style while retaining the table; Convert to Range removes table behavior while retaining the data and applied formatting.

Move or Copy Cells in Excel

Save a copy of the workbook first. Select one cell or a contiguous rectangular range, choose Home > Cut to move it or Home > Copy to keep the original, then select the upper-left destination cell and choose Home > Paste. Windows shortcuts are Ctrl+X or Ctrl+C, then Ctrl+V; on Mac use Command+X or Command+C, then Command+V. Inspect the entire destination area first: pasting can replace existing data, and copying may not warn you. Use an empty area or cancel if the proposed destination is wrong. Esc cancels a pending cut/copy; Undo can reverse an unintended paste before further changes. Microsoft’s cell-moving guide distinguishes replacing cells from inserting whole rows or columns.

After a move, check that the intended contents reached the destination and the source changed as expected; after a copy, check that the source remains. Review formulas and totals before saving. Copying a formula can adjust relative references, while moving a formula keeps its references; Microsoft’s formula guide explains the difference. Use Paste Values when you want only calculated values rather than formulas. This is not the same as merely moving the active-cell selection with the arrow keys, and a collection of nonadjacent cells is not one rectangular paste range.

Save an Image of an Excel Cell Range

Select only the intended cell range, rather than the worksheet’s entire grid. In desktop Excel, choose Home > Copy arrow > Copy as Picture, select As shown on screen and Picture, then OK. Microsoft documents copying a range as a static picture. Open Paint, paste the picture with Ctrl + V, and use File > Save as > PNG picture to choose a filename/location and save. Reopen the PNG to check that the range is complete, readable and contains no unintended information. Retain the Excel workbook: the image has no editable formulas or live data connection and will not change with the cells. For a large sheet, select a useful smaller range instead of assuming one image will keep every row readable. You can hide worksheet gridlines before copying if desired.

Paste Calculated Values Without Formulas

Copy the intended source cells with Ctrl + C and select the top-left destination cell in an empty area large enough for the copied range. Choose Home > Paste arrow > Values (the 123 icon), or Paste Special > Values > OK in the desktop dialog. Check the destination against the source and inspect its formula bar: it should contain the result, rather than the original formula. The source formulas remain intact. Pasted values are static and will not recalculate when their former inputs change; their number formatting may differ unless you choose an appropriate values-and-formatting option. Save after checking the results, or Undo promptly if the destination or paste choice was wrong. This is useful for retaining calculated numbers while excluding formulas, rather than creating a picture or moving the source cells.

Edit the Contents of an Existing Excel Cell

Select the cell and press F2, double-click inside it, or click its contents in the formula bar. Position the cursor, select only the characters you want to replace, and type the correction. Press Enter to commit it, then check the cell and any affected totals or formulas. Typing directly into a selected cell without entering edit mode replaces its contents instead. Microsoft’s cell editing instructions cover these choices. Press Esc before committing to cancel, or Ctrl+Z promptly after committing to undo. Save the workbook with Ctrl+S when applicable, or verify AutoSave status. Committing an edit is separate from saving the file. Check for an existing formula before replacing it, and obtain appropriate access if the worksheet or cell is protected.

Number Excel Columns or Rows With a Fill Series

Use an empty area or a backup so you do not replace data. To number cells horizontally in Excel 2010 or current desktop Excel, enter 1 and 2 in the first two adjacent cells, select both, then point at the small fill handle at the bottom-right of the selection. Drag it right through the intended cells and release. Check the first values and final number. For a vertical series, put the two starting numbers one above the other and drag down. Microsoft’s Auto Fill guide documents the two-value pattern.

For even numbers, start with 2 and 4 instead. If Excel copies rather than continues the pattern, open Auto Fill Options and choose Fill Series when offered. One starting number can be copied repeatedly, which is why selecting both seeds matters. These are values in worksheet cells; this does not rename the built-in A/B/C column labels or make the numbers update automatically after every insertion. Inspect the result, Undo an unwanted fill promptly and save after checking the destination.

Convert Excel Text to Uppercase Without an All Caps Checkbox

Excel does not provide the Word-style All Caps font effect described in some tutorials. To convert text, preserve the original and choose an empty helper cell: for text in A2, enter =UPPER(A2) in B2 and press Enter. Check the uppercase result, then fill the formula down beside the remaining source rows using the fill-handle procedure below. Use LOWER for lowercase or PROPER for initial capitals when that is the desired transformation. Microsoft’s text-case guide explains these functions. They calculate new text, rather than just displaying the original in another font effect.

Keep the helper results if you want them to update with the source. If you intentionally need to replace the originals, first make a recoverable copy, check every converted value, copy the helper range and use Paste > Values at the intended source range. Inspect the formula bar to confirm that these are now static text values before removing a helper column. A normal formula paste can create wrong references, so select Values deliberately. Proper-case rules can change names and acronyms in unwanted ways; review the actual results. Undo promptly for a wrong destination or retain the untouched backup.

Copy or Fill an Excel Formula

Start with an empty result cell: for numeric inputs in A2 and B2, enter =A2*B2 in C2 and press Enter. Check its result and formula bar. To copy it in Excel for Windows, select C2, press Ctrl+C, select the intended destination cell or range, and press Ctrl+V. Use Command+C and Command+V on Mac. Pasting replaces existing destination contents, so choose empty cells or make a recoverable copy first. Normal paste copies the formula and formatting; the Home > Paste menu also offers Formulas when you want formulas alone. Microsoft explains copying formulas and paste options. Paste Values instead creates static results, so it is unsuitable when you need copied calculations.

For adjacent rows, select the formula cell and drag its bottom-right fill handle through the intended destination cells. Another Windows desktop method is to select C2 and the empty cells below it, then choose Home > Fill > Down; other directions are available in that menu. Microsoft documents formula filling. Inspect C3: a relative formula should now read =A3*B3. Dollar signs can keep a reference fixed, as explained in our Excel formula-reference guide. Check the last copied formula and change a sample input to verify recalculation. Undo an unwanted paste immediately and save the intended workbook.

Increase an Excel Value by a Fixed Amount

Use a separate empty result cell to preserve the original number. If A1 contains 20, select B1, type =A1+5 and press Enter; B1 should show 25. Replace A1 with your actual input reference and 5 with the amount to add. Change A1 to 30 temporarily and check that B1 updates to 35, then Undo the test or keep the intended change and save. Do not put =A1+5 in A1 itself, because that refers to its own result. Microsoft’s cell-reference guide explains using inputs in calculations. For multiple rows, fill the result formula down using the formula-copying section below and inspect the first and last references. This calculates increased results without overwriting the source cells; Paste Values creates static numbers instead.

Create a Simple Excel Formula

In a blank area of your worksheet, enter 10 in A1 and 20 in B1. Select C1, type =A1+B1, and press Enter; C1 should show 30. Select C1 again to inspect the formula in the formula bar. Change B1 to 25 and check that C1 becomes 35, then undo the test or save the workbook if you want to keep it. Use + to add, - to subtract, * to multiply and / to divide; parentheses change the order of calculation. Microsoft’s simple-formula guide explains these operators and cell references. If Excel displays the formula as text, remove an initial apostrophe or change the cell from Text to General and re-enter it. Check references and calculation settings if the result does not update. The SUM and AVERAGE steps elsewhere in this guide are useful next examples.

Create a Simple Monthly Calendar in Excel

Create and name a blank workbook using the steps above. Put Sunday through Saturday in B1:H1 and reserve B2:H7 for six possible weeks. Check the weekday on which the month starts, place 1 in that column in row 2, and continue the day numbers across each week through the month’s last day. Leave unused cells before 1 and after the final date blank. For October 2026, Thursday is column F in this layout, so 1 belongs in F2. Verify the first and last dates against a calendar; copying this grid for another month requires realigning the dates.

Select the grid, make headings bold, add borders and use Home > Wrap Text. Drag column and row boundaries to provide readable space. In Windows Excel, edit a day cell and use Alt+Enter for a note beneath its number. Save as .xlsx, reopen and check the month, entries and print preview. For a prepared calendar instead, choose File > New, search online templates for calendar, select one and choose Create, following Microsoft’s calendar-template instructions. Preserve its formulas when changing the month/year controls. A manual grid is not a calendar subscription or reminder service.

Extend the Calendar to a Full Year

For a manual annual calendar, create the monthly grid above, then add eleven worksheets with the plus control beside the sheet tabs. Double-click each tab to name it for a different month. Build the same six-week layout on each sheet and enter that month’s dates for the intended year. Check the weekday of every first date, each month’s final date, and whether February has 28 or 29 days; repeating one month’s date positions does not create a correct year. Use Home’s font and fill-color controls for headings or weekends, and add notes without replacing date values you need. This is a static calendar, not an automatic reminder system. Alternatively, Microsoft documents File > New, searching for calendar, choosing a result and selecting Create in its calendar-template guide. Choose a yearly design if offered, set its year using its own instructions, and preserve any date formulas. Save as .xlsx, reopen the file and inspect all twelve months. Check File > Print, including the whole-workbook choice when printing every monthly sheet, before printing.

Show or Hide the Excel Formula Bar

In Excel for Windows, open the View tab and, in the Show group, select the Formula Bar checkbox. The bar appears above the worksheet grid; select a cell to check that it displays the cell’s value or formula. Clear the same checkbox to hide it again. Microsoft documents this View control. This includes the older Excel 2013 ribbon layout. Showing the formula bar does not change the formulas, calculate new results or switch worksheet cells to formula text; the separate Formulas > Show Formulas command controls that worksheet display. A formula deliberately hidden by worksheet protection may still be concealed.

Remove an Excel PivotTable

Save a backup of the workbook first. Click inside the intended PivotTable, open its PivotTable Analyze tab (Analyze under PivotTable Tools in older Excel versions), and choose Select > Entire PivotTable. Check that the selection covers the whole PivotTable, then press Delete. Microsoft describes deleting the complete PivotTable. Excel does not allow deleting just an arbitrary part of its result cells. Confirm that the PivotTable is gone and that the source data remains. Use Undo immediately if you removed the wrong item, or restore the backup if needed. Deleting an entire worksheet is a different action that also removes anything else on that sheet; it is unnecessary when you want to remove only the PivotTable.

Insert an Older Form Control Checkbox

In desktop Excel for Windows, open File > Options > Customize Ribbon, check Developer and choose OK. Select Developer > Insert > Form Controls > Check Box, then click or draw it at the intended worksheet location. Right-click the control and use Edit Text to change its label. To record its state, right-click it, choose Format Control > Control, set Cell link to an unused cell such as $G$2 and confirm OK. Click the checkbox and verify that its linked cell changes between TRUE and FALSE. Microsoft documents Form Controls and their desktop requirements. Choose a separate link cell for each independent checkbox and save the workbook. This is an object positioned over the worksheet, unlike the newer Insert > Checkbox feature above; it does not require ActiveX or a macro. To remove only the control, right-click it and press Delete, then inspect its former link cell separately.

Convert Millimeters to Inches in Excel

Keep the millimeter inputs and results in separate cells. Enter the numeric value 25.4 in A2, then enter =A2/25.4 in an empty B2 and press Enter; the result should be 1 inch. NIST defines an inch as exactly 25.4 millimeters. Replace A2 with your actual input reference and use your spreadsheet locale’s decimal separator where necessary. For more measurements, drag B2’s fill handle down beside the input rows and inspect the next and last formulas; the references should advance to A3, A4 and so on. Format the results with enough decimal places for your purpose. Dividing performs the conversion; changing a number format or adding an inches label alone does not. Keep the formula out of its own input cell and check that the measurements are numbers rather than text containing mm. To convert inches to millimeters, multiply by 25.4 instead.

Shade Every Other Row With Conditional Formatting

Select only the range to shade, excluding a heading row if it should keep its own formatting. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =MOD(ROW(),2)=0, choose Format > Fill, select a readable color and confirm OK in both dialogs. Microsoft documents this alternating-row rule. Check that even-numbered worksheet rows within your selection are shaded; use =MOD(ROW(),2)=1 for odd rows. This uses worksheet row numbers, so filtering rows does not necessarily alternate the visible records. In Conditional Formatting > Manage Rules, inspect the rule’s Applies to range when adding records. To reverse it, delete this particular rule there rather than clearing unrelated formatting. A structured table’s Banded Rows setting, explained above, is a different option that expands with its table.

Enter Quotation Marks in an Excel Cell

To store an ordinary quotation as text, select an empty cell, type "Hello" and press Enter. Check that both quotation marks remain. You do not need an initial apostrophe for an ordinary sentence. An initial apostrophe can deliberately force text entry, for example '"Hello"; it is not displayed in the worksheet cell but may remain visible in the formula bar. For a formula that produces quotation marks, enter ="He said ""Hello""." in an empty cell; doubled quotation marks inside the formula’s text represent literal quotes, so its result should be He said "Hello".. Microsoft explains quoting text in formulas. Use straight quotation marks when typing formulas. Press Enter to commit the cell and save the workbook separately. Text entry and a formula returning text differ from storing a numeric measurement for arithmetic.

Create a Simple Inventory List in Excel

Use File > New > Blank workbook, then enter Item, Quantity, Unit Price and Value in A1:D1. Add a SKU or supplier column if useful. Enter one item per row, keeping quantities and prices numeric. For quantity 3 in B2 and unit price 10 in C2, enter =B2*C2 in D2; check that the value is 30. Fill that formula down using the copying procedure above and inspect its references. This is a manual stock list: entering an item does not automatically deduct sales or connect to purchasing software.

Select the populated range and use the Insert > Table procedure above, confirming its header row. Format prices and values as currency, wrap descriptions and adjust column widths. Use the table filters to inspect an item and the Total Row to total visible values when needed. Choose File > Save As, name the workbook Inventory and save as .xlsx; reopen it and check the records and calculations. A local workbook name is set during saving, while its worksheet tab has a separate name. Keep a recoverable copy before large edits, and update quantities deliberately rather than assuming this simple list provides live stock counts.

Calculate an Absolute Value With ABS

Preserve the original number and use an empty result cell. For -25 in A2, enter =ABS(A2) in B2 and press Enter; the result is 25. Microsoft defines ABS as a number without its sign. Fill B2 down beside other inputs using the formula-copying procedure above, then check the next and final references. ABS leaves an already-positive value positive; =A2*-1 instead reverses either sign. Neither is the negative-number display format discussed above. Keep the original values when their signs matter, and do not enter a formula referring to A2 inside A2 itself. Check recalculation with a test input, undo the test if needed and save.

Cross Off Checklist Tasks With Linked Checkboxes

Enter tasks in A2:A10, then use the older Form Control Checkbox instructions above to place a checkbox beside each task. Give each control its own unused linked cell, for example G2 through G10, and verify that clicking it changes that cell between TRUE and FALSE. One shared link cell will not track independent tasks. You can change the controls’ labels with Edit Text; a checkbox’s drawn position is separate from its linked value.

Select A2:A10, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =$G2=TRUE. Choose Format > Font > Strikethrough, then confirm both dialogs. Microsoft explains formula-based conditional formatting; this example fixes column G while letting each task use its own row. Check and uncheck two different controls to verify that only their respective tasks are crossed off. Inspect the rule’s Applies to range if adding tasks, and save. Delete this particular rule in Manage Rules to remove the automatic strike effect. Modern in-cell checkboxes above use their own cells as TRUE/FALSE values instead.

Remove or Apply Strikethrough in Excel

Select the intended cells, open Home > Font dialog launcher or press Ctrl+1, and choose the Font tab. Under Effects, clear Strikethrough to remove the line, or check it to add one, then choose OK. Microsoft documents these font effects. This preserves the older Excel 2010 dialog method. Check the selected cells and save; if a mixed selection is confusing, inspect one known cell first. Removing a font effect leaves its text and other formatting intact, unlike Clear All or deleting the contents.

If the line returns because a conditional-formatting rule creates it, changing the direct Font checkbox may not remove the visible result. Use Home > Conditional Formatting > Manage Rules, inspect the rules applying to those cells and edit or remove only the intended rule. For the linked-checkbox checklist above, unchecking its corresponding control can make the rule false without removing it. Verify the result and other tasks before saving. Do not clear every formatting rule merely to remove one line, and keep the workbook copy if the intended rule or data is uncertain.

Calculate a Median in Excel

Select an empty result cell outside the numeric input range, enter =MEDIAN(A2:A6) with your actual range and press Enter. This also works in Excel 2013. For values 1, 2, 3, 4 and 100, check that the result is 3, rather than their arithmetic average. Microsoft explains that MEDIAN uses the middle value, or the average of the two middle values for an even count. Referenced blank cells and text are ignored; numeric zero counts. Inspect the formula bar and its range, change a sample input to test recalculation, then undo the test and save. Paste Values makes a static copy if that is intended; keep the formula when you want updates. Adding data outside the referenced range may require extending it.

Count Numeric Ranges With FREQUENCY

Put numeric observations in A2:A6 and ascending upper bin limits in C2:C3. For a small check, use observations 1, 2, 2, 4 and 7, with bin limits 2 and 5. In current dynamic-array Excel, enter =FREQUENCY(A2:A6,C2:C3) in an empty D2 and press Enter. Keep D2:D4 clear so the three results can spill. They should be 3, 1 and 1: values up to 2, greater than 2 through 5, and greater than 5. Microsoft documents the extra result above the highest bin. This counts numeric intervals, rather than counting a particular word.

For legacy Excel without dynamic arrays, first select the entire empty output range D2:D4, type the same formula and confirm with Ctrl+Shift+Enter; do not type the array braces yourself. Use one more output cell than there are bin limits. FREQUENCY ignores blanks and text in the source. Replace both ranges with your actual data and bins, verify a few counts manually, and check that the counts total the intended observations. A blocked spill needs a clear output area in modern Excel; overwriting existing data is unnecessary. Save after checking the first and final intervals.

Calculate NPV With the Initial Cash Flow in the Right Place

For an equal-period example, put the chosen per-period discount rate 10% in B1, the immediate initial payment -100 in B2, and future end-of-period net receipts 60 and 60 in B3:B4. Select an empty result cell and enter =NPV(B1,B3:B4)+B2, then press Enter. The result is approximately 4.13. Microsoft explains that an immediate initial cash flow belongs outside the NPV arguments. Do not include B2 in the future range and add it again. Enter income as positive and payments as negative; if using separate inflow and outflow columns, calculate net flow as inflow minus outflow first.

Keep cash flows chronological and the rate consistent with each period; the 10% example is an illustration, not a rate recommendation. NPV assumes equally spaced end-of-period amounts. Use the separately documented XNPV function for irregular dated cash flows. Check the example against =60/(1+B1)+60/(1+B1)^2+B2, inspect ranges and signs, and save the workbook. Blank or text entries are not substitutes for an intended zero cash flow. A positive result reflects the inputs and assumptions; it does not by itself establish that a real investment is suitable or that projected receipts will occur.

Display Large Excel Numbers in Millions

In desktop Excel, select the numeric cells, press Ctrl+1 on Windows or Command+1 on Mac, and choose Number > Custom. In Type, enter #,##0.0,, for one decimal place in millions, or 0.0,,"M" to append M, then choose OK. For stored value 2500000, check for 2.5 or 2.5M. Microsoft documents comma scaling and quoted text. Two trailing commas scale the display by a million; the original numeric value remains available to formulas and visible in the formula bar. Adjust decimal placeholders for the desired precision, account for regional separators and label the unit clearly. Restore General or the previous format to undo the display change. This does not divide the stored data, and Excel for the web does not create the same custom formats.

Frequently Asked Questions

Can I make this in Excel on the web?

Yes. Tables, the total row, and filters work in Excel for the web too.

Why does my total change when I filter?

The total row uses SUBTOTAL, which ignores hidden rows. Clear the filter to see the full total.

How do I add more people later?

Type in the first empty row right below the table. Excel expands the table and includes the new row in the total.

Merge Excel Cells Without Losing Other Values

Make a recoverable copy and use merging for a heading or label rather than ordinary data records. In desktop Excel, select the intended adjacent cells, open Home > Merge & Center and choose Merge Cells to merge without centering, or Merge & Center for a centered heading. Merge Across merges each selected row separately. Keep only the intended upper-left value in the selection first: merging does not concatenate the text, and other selected values are discarded. Microsoft’s merging guide describes that behavior. Cells inside a structured Excel Table cannot be merged; preferably place a heading outside the table. If you deliberately need a regular range instead, review the table-to-range procedure and its feature changes first. Check the remaining text and layout. To undo immediately use Ctrl+Z; later select the merged cell and choose Merge & Center > Unmerge Cells. Unmerging alone does not recover discarded values. Save only after checking the intended result.

To preserve text from more than one cell, combine the text before merging a display area. For source text in A2 and B2, enter =A2&" "&B2 in an empty C2 and press Enter; inspect the result against both originals. Microsoft documents joining cell text with &. To make a wider merged label without deleting those sources, copy C2 and choose Home > Paste > Values into an empty D2, first checking that D2:E2 contain nothing you need. Confirm D2 contains the complete static text, then select D2:E2 and use Merge & Center as described above. Check that A2 and B2 remain intact and the merged label contains both texts. Paste Values makes a snapshot that will not update with later source changes; Microsoft explains formula-to-value conversion. Keep the saved copy and use Undo immediately for an unwanted paste or merge. For many rows, use our full text-combination and fill-down guide. Merging original nonempty cells directly still discards all but the upper-left value.

More Excel Guides

• How to calculate percentage in Excel
• How to AutoFit columns and rows in Excel
• How to center a worksheet horizontally and vertically in Excel