The simplest formula: INDEX + RANDBETWEEN
For a clean list with one quote in every row, combine RANDBETWEEN with INDEX. RANDBETWEEN supplies a random row number; INDEX returns the quote stored at that position.
=INDEX(A2:A100,RANDBETWEEN(1,COUNTA(A2:A100)))Build the list
Use one column for quote text. Avoid putting a blank row inside the active list when you use the basic formula.
Paste the formula
Enter the formula in a result cell such as C2. Excel returns one quote from the selected range.
Refresh the result
Press F9, edit a referenced cell, or recalculate the workbook to draw another quote.
Use an Excel Table for a list that grows
An Excel Table is usually easier to maintain than a fixed range. Select your quote list, choose Insert → Table, and name the table Quotes. If the quote column is named Text, the formula becomes:
=INDEX(Quotes[Text],RANDBETWEEN(1,ROWS(Quotes[Text])))When you add a new row to the table, the structured reference expands with it. That means you do not need to rewrite A2:A100 every time your collection grows.
Avoid blank rows with FILTER
The basic COUNTA pattern is best when every row is filled. If your list contains empty cells, first filter the range to nonblank values, then choose a random item from the filtered array. This version is designed for Excel versions that support FILTER and LET:
=LET(list,FILTER(A2:A100,A2:A100<>""),INDEX(list,RANDBETWEEN(1,ROWS(list))))- FILTER removes empty cells before the random position is calculated, so a blank row is not returned as the quote.
- LET stores the filtered list once, which keeps the formula easier to read than repeating FILTER several times.
- If the range has no quote at all, FILTER returns an error. Keep at least one nonblank quote in the source range or add an IFERROR message around the formula.
Understand refresh, recalculation, and fixed results
RANDBETWEEN is a volatile random function: Excel may recalculate it when the workbook recalculates. Press F9 when you want a new motivational quote without changing the list. If the value does not change, check Formulas → Calculation Options and make sure the workbook is using Automatic calculation.
A random formula is useful while brainstorming, presenting a daily prompt, or testing a quote list. It is not ideal when you need a permanent result. Once you like the selected line, copy the result cell and use Paste Special → Values. The cell will keep the quote text instead of recalculating to another result.
Before relying on the output, test the sheet with a few simple rows. Keep the header outside the source range, put only one quote in each row, and make sure the result cell is not included in the list. If you sort or edit the source list later, the formula will select from the current range; a value pasted as a final result will stay unchanged.
This setup also works well for a morning prompt, a classroom warm-up, a team check-in, or a personal journal. Keep the source list focused on one audience or mood when you want the random result to feel consistent. A separate worksheet can hold the library, while the visible result cell stays clean for printing or sharing.
- Use F9 for another selection, and Ctrl + Alt + F9 when you need a full workbook recalculation.
- Keep the source list separate from the result cell so that copying the final quote does not overwrite your library.
- If you share the workbook, label generated cells clearly so readers know the result is randomly selected from your list.
Need several quotes without repeats? Shuffle the list
INDEX + RANDBETWEEN can select the same quote again on the next recalculation. For a rotating set of unique results, Excel 365 users can filter the list and sort it by a random array:
=SORTBY(FILTER(A2:A100,A2:A100<>""),RANDARRAY(ROWS(FILTER(A2:A100,A2:A100<>""))))- The formula spills a shuffled list. Read the first row for one quote, or take the first few rows for a batch of different quotes.
- Because the list is randomized again during recalculation, copy and paste values when you want to preserve that particular order.
- This is an Excel 365-style approach. Older Excel versions can use a helper column with RAND(), sort the rows, and then copy the top results.
Excel formula or an online quote generator?
The right choice depends on whether you need a repeatable worksheet or a ready-made quote. Excel is best when the wording, order, and ownership of the source list matter; an online generator is faster when you want fresh ideas without maintaining a spreadsheet.
| Need | Best fit | Why |
|---|---|---|
| Choose from your private quote library | Excel formula | Your list stays in your workbook and can be edited, filtered, or shared with a team. |
| Generate a new motivational idea from a topic | AI motivational quote generator | Describe the goal, audience, or challenge instead of preparing a source list. |
| Browse varied categories quickly | Random quote generator | Use category filters and copy several original lines without setting up formulas. |
| Turn a paragraph into a shorter quote | Quote generator from text | Start with existing notes or prose and ask for a concise quote draft. |
Random motivational quotes in Excel FAQ
Official Excel function references
- Microsoft Support: INDEX function — Reference for returning a value or reference from a table or range.
- Microsoft Support: RANDBETWEEN function — Reference for returning a random integer between two specified values.
- Microsoft Support: Recalculate a formula — Guidance for calculation modes and recalculating formulas in Excel.
Want new motivational ideas instead of a fixed list?
Use the motivational quote generator when you want to describe a goal or challenge and receive an original draft to edit.
Open the Motivational Quote Generator