Note: The other languages of the website are Google-translated. Back to English
English English

How to sum top n values in Excel table without using any formulas?

It is common for us to use formulas to sum the largest n values in an Excel table. But what if we feel tired of remembering the formulas and want an easier method? Please don’t worry. In this tutorial, we will show you a simple way to sum top n values in an Excel table, with no need to use any formulas.
doc sum-top-n-values-without-formulas 1

Sum top n values in Excel table without using any formulas


In the Excel table below, there is a list of numbers that represent the company's sales for each month of the year. To sum the top 3 values in the Excel table without formulas, please do as follows.

1. Select the Excel table you want to sum the largest 3 values.

2. Go to the Table Design tab, and check the Total Row option in the Table Style Options group to enable the feature.
doc sum-top-n-values-without-formulas 2

3. The total sum of the values are returned below the Excel table.
doc sum-top-n-values-without-formulas 3

4. Click on the filter icon in the header of column B, which contains the numeric values. Then click Number Filters > Top 10 in the menu list.
doc sum-top-n-values-without-formulas 4

5. The Top 10 AutoFilter dialog box pops up. Specify the number of top items to display. Here I change the default number 10 to 3.
doc sum-top-n-values-without-formulas 5
doc sum-top-n-values-without-formulas 6

6. Click the OK button. The top 3 values are displayed and the sum is automatically calculated in the Excel table.
doc sum-top-n-values-without-formulas 7


Other Operations (Articles)

How To Quickly Insert A Line Based On Current Date In Excel?
Supposing there is a sheet containing a row with dates, and now I want to insert a line right to the current date which will auto change while opening the workbook every day. Is there a trick can solve it in Excel?

How To Always Insert A Blank Row Below Instead Of Above In Excel?
As we all known, when inserting blank row in a worksheet, the blank row will always be inserted above the selected row or cell. But, sometimes, you may need to insert the row below the selected cell or row. How could you solve this job in Excel?

How To Insert A Blank New Row Automatically By Command Button In Excel?
In many cases, you may need to insert a blank new row into a specified position of your worksheet. In this article, we will show you how to insert a blank new row automatically by clicking on a Command Button in Excel.

How To Insert A Blank Row After Specific Text In Excel?
If you want to insert blank rows after a specific text as following screenshot shown, how to deal with it quickly and easily without inserting them manually one by one?



  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations