Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 93 ยท Getting Started

Templates, Quick Analysis, Autofill and Small Time-Savers

Use Excel templates, Quick Analysis and Autofill series, Insert Function, Scroll Lock, and quick fixes: swap columns, delete blank rows, delete rows below the active cell, wrap text and fractions.

A lot of the speed in Excel comes from small features that are easy to miss: starting from a ready-made template, letting Quick Analysis suggest a chart, Autofill extending a pattern, or swapping two columns in one drag. Individually each saves seconds; together they change how fast you work. This lesson collects the ones that are genuinely useful, with exact steps.

Excel cannot run on this site, so the menus are real Excel but read-only. The TryIts are JavaScript stand-ins that model two behaviors worth understanding: how Autofill guesses the next values of a series, and how Excel turns a decimal into a fraction format. They are teaching models, not Excel's code. For keyboard shortcuts see Shortcuts and Productivity.

Templates

A template is a workbook set up in advance: formulas, formatting and layout are done, and you fill in your own data. To use one: File > New, then browse the featured templates (budgets, invoices, calendars, schedules, expense reports) or search online. Select one and click Create; Excel opens a copy, so the original template is untouched.

To make your own, build the workbook (put inputs in clearly marked cells, protect the formula cells, see Collaboration and Protection) and choose File > Save As > Excel Template (*.xltx); with macros use .xltm. Excel saves it into your Custom Office Templates folder, and it then appears on the File > New > Personal tab. Opening a template creates a new workbook named "MyTemplate1", which prevents overwriting the original. A good template has a short Instructions sheet.

The Book.xltx and Sheet.xltx templates are special: if you save a file with one of those names in the XLSTART folder, they become the default for every new workbook or new sheet, with your fonts, column widths and number formats.

Quick Analysis

Select a range of data and a small Quick Analysis button appears at its bottom-right corner (or press Ctrl+Q). It has tabs:

  • Formatting: data bars, color scales, icon sets, "greater than" rules (Conditional Formatting).
  • Charts: recommended charts for the data, with live previews.
  • Totals: a running sum, average, count, % total or running total as a new row or column.
  • Tables: insert a Table or a PivotTable.
  • Sparklines: line, column or win/loss mini-charts (Sparklines).

It is a quick way to try ideas, but nothing in it is special: every result is an ordinary feature you can apply yourself. If the button does not appear, check File > Options > General > Show Quick Analysis options on selection.

Autofill

The fill handle is the small square at the bottom-right of a selected cell or range. Drag it, or double-click it to fill down as far as the neighboring column has data.

Start with Drag gives
Mon Tue, Wed, ... (built-in lists for days and months)
Jan-25 Feb-25, Mar-25
1 (one cell) 1, 1, 1 (copy)
1 and 2 (two cells selected) 3, 4, 5 (linear series)
10 and 20 30, 40, 50
Item 1 Item 2, Item 3
a formula the formula with relative references adjusted (Cell References)

After dragging, the Auto Fill Options button offers Copy Cells, Fill Series, Fill Formatting Only, Fill Without Formatting, and for dates Fill Days, Weekdays, Months or Years. Hold Ctrl while dragging a single number to switch between copy and series. For more control choose Home > Fill > Series (linear, growth, date steps, and a stop value). Define your own sequences in File > Options > Advanced > Edit Custom Lists (Entering Data). For patterns in text, Flash Fill (Ctrl+E) learns from an example (Cleaning Messy Data).

JS How Autofill guesses the next values

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Insert Function and function help

Click the fx button next to the formula bar (or Shift+F3) to open Insert Function: search by what you want to do ("round to nearest 5"), pick a category, and the dialog explains each argument and shows a preview of the result. While typing a formula, Ctrl+A right after the function name opens the same argument dialog, and Ctrl+Shift+A inserts the argument names as placeholders. The ScreenTip under the cell lists the syntax; click a function name in it to open its help page.

Scroll Lock

When Scroll Lock is on (the status bar shows it), the arrow keys scroll the window instead of moving the active cell. People often hit it by accident and think their arrow keys are broken. Press the Scroll Lock key (or use the on-screen keyboard: Win+Ctrl+O) to turn it off.

Quick fixes you will use all the time

Swap two columns (or rows): select a column, hold Shift, drag its border until a thick vertical "I-beam" line appears at the destination, and release. The column inserts there instead of overwriting. (Without Shift, you replace the target.) For rows, same idea.

Delete blank rows or columns: select the column that identifies a complete row, Home > Find & Select > Go To Special > Blanks, then Home > Delete > Delete Sheet Rows. This deletes the entire row of every blank cell you selected, so use a column that is always filled when the row is real, or you may delete rows that have data in other columns. A safer method is a helper column =COUNTA(A2:H2), then filter for 0 and delete the visible rows (Filtering Data). In Power Query, Remove Rows > Remove Blank Rows does it repeatably.

Delete all rows below the active cell: click the first row you want gone, press Ctrl+Shift+Down Arrow to select to the bottom of the data (or Ctrl+Shift+End to the last used cell), then Ctrl+- and choose Entire row. To remove the leftover empty formatting, save and close, or reset Used Range by deleting the rows rather than clearing them.

Wrap text and AutoFit: Home > Wrap Text makes long text break onto several lines inside the cell; Excel then increases the row height automatically if you have not set it manually. AutoFit sizes a column to its longest value: double-click the border between column headers (or Home > Format > AutoFit Column Width). To fit every column at once, select the whole sheet (Ctrl+A) first. Merged cells defeat AutoFit; prefer Center Across Selection (Format Cells > Alignment > Horizontal).

Copy a value down a diagonal or to every other cell: type the value in the first cell, then use Go To Special or select the cells you want with Ctrl+click, type the value and press Ctrl+Enter to enter it into all the selected cells at once. For a diagonal band, Ctrl+click each cell (or use a formula such as =IF(ROW()=COLUMN(),1,0) in a block).

Show numbers as fractions: choose Format Cells > Number > Fraction and pick "Up to one digit (1/4)", "Up to two digits (21/25)" or "As halves, quarters, eighths...". Typing 0 1/2 (a zero, a space, then 1/2) enters one half; without the leading 0 , Excel may read 1/2 as the date 2 January. The cell still holds the decimal 0.5, only the display changes.

JS The closest fraction with a given number of denominator digits

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Split a cell: a cell cannot be split in the way you split a merged cell, but you can split text into several columns with Data > Text to Columns or the TEXTSPLIT function (Joining and Splitting Text). To undo a merge, select the merged cell and choose Merge & Center again (Unmerge Cells).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which shortcut opens Quick Analysis for the selected data?

    Ctrl+Q opens the Quick Analysis gallery (the button at the bottom-right of the selection does the same).
  2. 2. How do you swap two adjacent columns without overwriting data?

    Holding Shift while dragging inserts the column at the drop position and shifts the others, instead of replacing the target column.
  3. 3. You select cells with the first two values 10 and 20 and drag the fill handle. What appears?

    With two numbers Excel detects the step (10) and continues the linear series.
  4. 4. What does the Fraction number format change?

    A number format changes the display only. The cell still holds the decimal value and works in calculations.

Summary

  • Templates (File > New or .xltx) save setup time; Quick Analysis (Ctrl+Q) offers formats, charts, totals and sparklines in one place.
  • The fill handle continues lists and patterns; select two cells for a linear series, or use Home > Fill > Series.
  • Shift-drag swaps columns; Go To Special > Blanks deletes blank rows (carefully); Ctrl+Shift+Down then Ctrl+- removes rows below a point.
  • Wrap Text and AutoFit fix cropped text; Fraction formats only change the display.
  • If arrow keys scroll the window, turn Scroll Lock off.

Next: Your First Formulas.