To shuffle bars in an Excel graph into a random order, add a helper column with =RAND(), replace the generated numbers with fixed values, and sort the entire source table by that column. A chart based on the table will follow the new row order. Make a copy of the workbook first, and decide whether you want a one-time random order or a repeatable custom order.
What You’ll Need
- An Excel workbook containing the category names and values used by your chart.
- A chart linked to those source cells, or the ability to select the source data and create a chart.
- A spare column for random sort keys. If the data is in an Excel table, add a new table column for the keys.
- Excel for Microsoft 365 on Windows for the steps below. The core sorting and RAND steps also work in current Excel for Mac and Excel for the web, although some ribbon labels or dialog layouts may differ.
Step-by-Step
- Check what the chart is connected to. Click the chart and look at the highlighted source range on the worksheet. Identify the category-label column and every value column that belongs to each category. For example, a chart might use product names in A2:A11 and sales in B2:B11. If the chart uses multiple series, all the series values for a category must stay on the same row as that category. Sorting only the names would scramble the meaning of the graph.
- Save a working copy and add a helper column. Use File > Save As to create a copy if the data is important or shared. Add a heading such as Random key in the next empty column beside the source data. Do not put the helper column inside the chart as a plotted series. If your source is an Excel table, type the heading in the next table header cell so the new column becomes part of the table.
- Generate a random number for each category. With category labels in A2:A11, values in B2:B11, and the helper column in C, enter this in C2:
=RAND(). RAND returns a decimal from 0 up to, but not including, 1. Fill the formula down through C11 using the fill handle, or copy C2 and paste it into C3:C11. Each row now has a random sort key. If you have more than one value column, keep all of those columns beside the labels and include them in the sort range. - Make the random keys stay put. RAND is a volatile function: Excel recalculates it when the workbook recalculates, so the numbers can change. Select C2:C11, copy it, and use Home > Paste > Paste Values. This replaces the formulas with the numbers currently displayed. In Excel for Mac, use Home > Paste > Paste Special and choose Values if the Paste Values control is not visible. In Excel for the web, use the Paste Values option on the Home tab or the paste menu. Keeping fixed keys prevents a later recalculation from silently changing the intended sort keys.
- Sort complete rows by the helper column. Select a cell inside the source data, then choose Data > Sort. In the Sort dialog, select the Random key column under Sort by, choose Values, and choose Smallest to Largest. If the range has headings, make sure My data has headers is selected. When Excel asks whether to expand the selection, choose Expand the selection. That choice is essential: it moves each label and its associated values together. If the source is a table, sorting the Random key header from smallest to largest will sort its rows as well.
- Check the chart order and clean up the helper column. The chart should now display its categories in the sorted source order. Confirm that each bar still represents the right category and value. You can hide the Random key column by right-clicking its worksheet column heading and choosing Hide, or leave it visible so the order can be checked later. Hiding it does not normally remove it from the chart’s source data; if the chart changes, inspect Select Data and confirm the plotted ranges still point to the intended columns.
- Shuffle again when you need a new order. Unhide the helper column if necessary, enter or fill
=RAND()again for the rows, and repeat the copy, Paste Values, and sort steps. Paste Values before sorting each time. Otherwise, a recalculation can change the keys and make the displayed values look unrelated to the order you intended to preserve. If you need a particular order rather than a random one, replace random keys with numbered positions such as 1, 2, 3 and sort by that column instead.
If you are starting without a chart, sort the data first, select the category and value columns without the random-key column, then choose Insert and select a bar or column chart. Excel will use the row order in the selected data. In Excel for Mac, the Insert tab also contains the chart controls; Excel for the web has a similar Insert tab, though chart options are more limited.
Common Mistakes
- Sorting just the labels. This separates category names from the numbers they describe. Always sort the entire source range, and choose Expand the selection if Excel prompts you.
- Leaving RAND formulas live. Random keys can change during recalculation. Freeze them with Paste Values before sorting if you want to keep that shuffle.
- Including the helper column as a chart series. Random decimals can appear as unwanted bars or distort the chart scale. If that happens, click the chart, choose Chart Design > Select Data, and remove the Random key series while keeping the category and value ranges.
- Expecting the same random order on every refresh. A new set of random keys produces another order. For an order that must remain consistent, use a manually numbered sort column or retain the frozen random keys.
- Confusing reversed display with a new shuffle. A horizontal bar chart can display the first source category at the bottom, depending on axis settings. That is an axis display option, not a change to the worksheet row order.
Tips
- Turn the source data into a table. Select the data and use Home > Format as Table. Tables make it easier to keep rows together when sorting and usually expand when you add rows. Include the category and value columns; keep the random helper out of the chart’s plotted series.
- Use a deliberate order for presentations. Random ordering is useful for changing the visual sequence, but it can make comparisons harder. If you want a stable, chosen sequence, put position numbers beside the categories and sort by those numbers. You can use gaps such as 10, 20, and 30 to leave room for inserting items later.
- Check the chart type before interpreting direction. In a vertical column chart, categories run along the horizontal axis. In a horizontal bar chart, categories run along the vertical axis. Sorting the source changes category sequence, but does not reverse the value axis or alter the values themselves.
- Keep a record if the shuffle matters. Once you have pasted the random keys as values, save the workbook. The key values preserve the order if you later need to sort the data back into the same sequence. Alternatively, copy the ordered category list to a separate sheet before generating another shuffle.
- Use recalculation deliberately. Press F9 in Excel for Windows to recalculate formulas; on many Mac keyboards, use Fn+F9, though keyboard settings can vary. Avoid recalculating while you are trying to preserve live RAND results. Pasting the keys as values is more reliable than trying to control recalculation.
Troubleshooting
The bars do not change order after sorting. First check that you sorted the same rows the chart uses. Click the chart, choose Chart Design > Select Data, and review the Chart data range and category labels. If the chart points to a separate range, sort that range or update the chart’s source. A chart based on a PivotTable may not follow an ordinary worksheet sort; change the order in the PivotTable or use a regular source range for the chart.
The bars change, but their labels and values no longer match. Undo immediately with Ctrl+Z in Windows, or Command+Z on Mac, if the sort just happened. Then select the complete dataset and sort again, expanding the selection when asked. If the file has already been saved or more edits have followed, restore a backup or compare the rows with the original data before proceeding.
Excel keeps changing the helper numbers. The cells probably still contain RAND formulas. Select the keys, copy them, and paste values over the same cells. Confirm by selecting one key cell and checking that the formula bar shows a number rather than a formula beginning with an equals sign. Then perform the sort.
The chart order looks backwards. For a horizontal bar chart, right-click the category axis, choose Format Axis, and under Axis Options adjust Categories in reverse order. The precise panel layout can vary by Excel version. This reverses the displayed category direction without randomizing the data. Check whether the horizontal value axis crosses at the opposite end afterward, and adjust its axis option only if that is also needed.
The sort includes the column heading or leaves out the last category. Open Data > Sort and check the My data has headers setting. Then verify the selected range reaches the final row of data. A table can reduce this problem because Excel identifies its header and rows automatically. If a category has a blank cell in the key column, enter a fixed key for it before sorting.
Frequently Asked Questions
Can Excel shuffle chart bars with one button?
No. Excel has no dedicated randomize-bars chart command. Randomize and sort the chart’s source rows using a helper column, or create a manual order column. The graph reflects the categories’ source order.
Will sorting the source data change the chart automatically?
Usually, yes, when the chart is linked to the cells you sorted. Check the chart after sorting, especially if it uses a PivotTable, a separate range, or data on another sheet. In those cases, its source or sort settings may need separate attention.
How do I shuffle only the bar order and not the values?
Keep each category label and its associated value or values together in one row, then sort the complete rows by the random-key column. The numbers stay paired with their original labels; only the sequence of the rows changes.
Can I keep the same random order?
Yes. Copy the RAND results and paste them as values before sorting, then save the workbook. The fixed keys provide a record of that order. Do not refill or recalculate the helper formulas unless you want a different shuffle.
Can I put the bars in a specific order instead?
Yes. Add a position column, assign each category its desired sequence number, and sort the complete source rows by that column from smallest to largest. This is more reliable than random keys when a report needs a consistent presentation order.
Does this method work for a horizontal bar chart and a column chart?
Yes. Both use category and value data, and both can reflect the source-row order. Their axes are arranged differently, so a horizontal bar chart may need its category axis reversed to make the first category appear at the top.
Related
- Resize Cells in Excel: Keyboard Shortcuts That Work
- Resize an Excel Table in Word: A Step-by-Step Guide
- Replace Words in Excel: Find and Replace Guide
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Use Excel Data to Create an Organization Chart in VisioYes. In the Visio desktop app for Windows, prepare an Excel worksheet with each person’s name and the…
- Geographical Location Chart in Excel: Map Chart GuideIn Excel for Microsoft 365 on Windows, select a table containing geographic areas and numeric values, then choose…
- Excel Scatter Plot With a Categorical X-Axis ExplainedAn Excel XY scatter plot cannot use text categories directly on its x-axis: the horizontal axis is a…
- Excel Pivot Table Months Not in Order? Fix the DatesIf Excel PivotTable months are out of order, first check whether the source column contains real dates or…
- Excel Pivot Chart Not Updating? Fixes That WorkIf an Excel PivotChart is not updating, refresh the PivotTable behind it: select the PivotTable and choose PivotTable…
- Excel Pivot Table Filter Showing Old Values? Fix ItIf an Excel PivotTable filter still lists values that have been removed from the source, refresh the PivotTable…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.