using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published April 20, 2022

How to Add Up a Column in Excel

Microsoft Excel is a robust records visualization and evaluation software that uses spreadsheets to store and organize units of information with formulation and functions. Excel is used by marketers, accountants, records analysts, and different professionals.

The MS-EXCEL software is part of the Microsoft Office suite. It is a multi-row and multi-column electronic spreadsheet used to organize data, visually portray data, and conduct various computations. It has 1048576 rows and 16383 columns, with each row and column forming a cell. Each cell has an address determined by the column name and row number, such as B7, Z100, and so on.

This tutorial covers how to sum the total of a whole column, a task many people need to do quite often. For example, if you are calculating up-to-the minute sales data, you will need to quickly understand the entire sum within the column to ascertain the sales results for the day.

Sum an entire column with only one click

You can sum columns in Microsoft Excel with just one click. Simply click on the column's name you want to add, and it will automatically highlight the column and sum values in that entire column. You can see the sum in the Excel status bar. The Excel status bar is at the bottom of the Microsoft Excel application.

The advantage of using the status bar for the sum value is that you also get the average value of all column values, and the count number of total values.

One more advantage is that if there is an empty cell or text-written cell between the valued cells, it will ignore that cell.

One disadvantage of using that method is that you can not copy the summed-up value.

You can also customize the status bar by right-clicking on the status bar. You can add maximum and minimum values, etc.

AutoSum formula to add column

The AutoSum function helps to sum an entire column with just a few steps:

There are two ways to use the Auto-Sum button.
You can find the button on the home tab and on the formulas tab.

On the Home tab, go to editing group and select the AutoSum function; or, you can go to the formula tab, and there you can see the AutoSum function in the function library group.
There are two methods to sum a column.
The first is to select the values of the cells by clicking and dragging the columns. Then, press the AutoSum button. The values of the selected columns will be added at the end of the sum value column.
The second method is to select the empty cell below the value and press the AutoSum button.
This will automatically add the sum function and highlight the range of the numbers.
Press Enter. You will get the total sum.

Note: one disadvantage of using the AutoSum function is that this function can not detect a blank cell or text written cells. If there are empty or text cells in between, the value's sum function will select the range below these cells.

You can also use this feature, to sum up the rows. Just select the next row of the values in row cells, and the sum function will show the range. Then press Enter, and you will get the result value in the new row.

Use the sum function to manually sum a column in Microsoft Excel

The AutoSum options are quicker and more accessible. There are some advantages to using manual functions to sum columns. You can add the sum function manually or sum only certain cells in a column, or specify an address for a wide range instead of selecting them manually.

Below are the steps to manually use the sum formula:

Firstly, select a cell anywhere on the Excel table where you want your sum value
Enter =sum( by keyboard keys, or you can also use the AutoSum function)

Next, select the range of numbers you want to sum
You can select the range of columns by selecting the first cell in the column and holding the shift key to select the last value cell
You can also select a separate cell by left-clicking on the cells while holding the Ctrl key
Go ahead and select the desired cells

Now, press the Enter key, and you will see your result in the selected cell.

Note that when using the sum function to get the sum of a column, any filtered or hidden cells are also included.

Using the sum function to add only visible cells

If you have a data table where you have hidden or filtered cells, you can not use the sum formula there. There are alternative steps to sum these values. Follow the steps below to sum visible cells only:

Hidden Cells

Function_num: This is a number that tells the AGGREGATE feature the calculation that wishes to be made. In this example, I actually have used nine, as I need the sum.

Options: You'll be able to specify what you wish to ignore when doing the calculation. In this example, I even have used 3, that ‘Ignores hidden row, error values, nested SUBTOTAL, and combination functions’. In short, it solely uses the visible cells for the calculation.

Array: Select the range of the cells from which you want the sum of their values. For example, D1:D3 is the range of cells that may also contain hidden cells.

=AGGREGATE(9,3,D1:D3) is the formula for the sum of the visible cells.

If you're using an older version of Excel, you can use the following formula:

Filtered Cells

With this formula, we can sum a column in Excel with filtered cells present there. This function is called (SUBTOTAL function).

=SUBTOTAL (9,D1:D3)

This formula does not have an optional function.

The IronXL C# Library

For the opening, reading, editing, and saving of Excel files in .NET, IronXL provides a versatile and powerful framework. It is compatible with all .NET project types, including Windows apps, ASP.NET MVC, and .NET Core Applications.

For .NET developers, IronXL provides a simple API for reading and writing Excel documents.

To access Excel manipulation scripts, IronXL does not require the installation of Microsoft Office Excel on your server, nor the use of Excel Interop. Working with Excel files in .NET becomes incredibly rapid and straightforward as a result of this.

Using IronXL, developers can perform all Excel-related calculations without any complication, by simply writing a few lines of code for tasks such as adding two cells, the grand total columns option, adding an entire column to an Excel table, adding an entire row to an Excel table, all the cells sum function/sum option and sum function of multiple columns and multiple rows, and many other helpful features.

Below are the code examples of the C# code in action.

using IronXL;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Set Formulas
worksheet["A1"].Formula = "Sum(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "Max(C3:C7)";

// Force recalculate all formula values in all sheets.  
workbook.EvaluateAll();

// Get Formulas
// Get the formula's calculated value.  e.g. "52"
string formulaValue = worksheet["G30"].Value;

//Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = worksheet["G30"].Formula;

//Save your changes with updated formulas and calculated values.
workbook.Save();

using IronXL;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Set Formulas
worksheet["A1"].Formula = "Sum(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "Max(C3:C7)";

// Force recalculate all formula values in all sheets.  
workbook.EvaluateAll();

// Get Formulas
// Get the formula's calculated value.  e.g. "52"
string formulaValue = worksheet["G30"].Value;

//Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = worksheet["G30"].Formula;

//Save your changes with updated formulas and calculated values.
workbook.Save();

Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private worksheet As WorkSheet = workbook.DefaultWorkSheet

' Set Formulas
Private worksheet("A1").Formula = "Sum(B8:C12)"
Private worksheet("B8").Formula = "=C9/C11"
Private worksheet("G30").Formula = "Max(C3:C7)"

' Force recalculate all formula values in all sheets.  
workbook.EvaluateAll()

' Get Formulas
' Get the formula's calculated value.  e.g. "52"
Dim formulaValue As String = worksheet("G30").Value

'Get the formula as a string. e.g. "Max(C3:C7)"
Dim formulaString As String = worksheet("G30").Formula

'Save your changes with updated formulas and calculated values.
workbook.Save()

VB C#

Developers have to be careful when they set out to modify and edit Excel files in C# because it can be easy for one misstep to change the whole document. Being able to rely on simple and efficient lines of code helps reduce the risk of error, and makes it easier for us to edit or delete Excel files programmatically. Today we walked through the steps necessary to edit Excel files in C# quickly and accurately, using functions that have already been well-tested. For more information, please visit the following link.