If you need to add up (sum) up race time splits that are some combination of hours, minutes, and seconds, you can simply use the SUM function. However, you must take care to enter times with the right syntax and use a suitable time format to display results, as explained below. Formula =SUM(range) Explanation The formula in cell H5 is:…
Tag: Format Cell
Clean and reformat telephone numbers using SUBSTITUTE function in Excel
One way to clean up and reformat telephone numbers is to strip out all extraneous characters, then apply Excel’s built-in telephone number format. Formula =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(A1,”(“,””), “)”,””),”-“,””),” “,””),”.”,””)+0 Note that the cell appears in the middle, with function names above and substitutions below. Not only does this make the formula easier to read, it also makes it easier to add…
How to create simple Time Sheet in Excel
This example teaches you how to create a simple timesheet calculator in Excel. Cells that contain formulas are colored light yellow. 1. To automatically calculate the next 4 days and dates when you enter a start date, use the formulas below. 2. Select the cells containing the times. 3. Right click, click Format Cells, and select the right Time format. Use the circled…
Convert Text to Numbers in Excel
By default, text is left-aligned and numbers are right-aligned in Excel. Atimes user enter inverted comma before inputting data values in a cell which automatically converts a number to text. This example teaches you how to convert ‘text strings that represent numbers’ to numbers. 1. Select the range A1:A4 and change the number format to General. 2. Numbers preceded by an apostrophe are also treated…
Currency vs Accounting Format in Excel
The Currency format and the Accounting format in Excel are very similar to each other. This example shows the difference. 1. Enter the following values. 2. The Currency format places the dollar sign right next to the number. 3. The Accounting format aligns the dollar signs at the left edge of the cell and displays a dash for zero values. Note: the Currency format can display…
Change Fractions Format in Excel
Excel always reduces fractions to its smallest denominator. This example teaches you how to enter a fraction in Excel and how change the format of a fraction. 1. To enter the mixed fraction 1 4/7 (one and four sevenths), type 1 4/7. To enter the fraction 4/7 (four sevenths) into cell A1, select cell A1 and type 0 4/7 (with a 0…
Apply Previous Cell Styles Using Format Painter in Excel
The Format Painter is one of the most underused features of Excel. The Format Painter copies formatting from one place and applies it to another. 1. For example, select cell B2 below. 2. On the Home tab, in the Clipboard group, click Format Painter. A moving dashed border appears around cell B2 and the mouse pointer changes to a plus and a paintbrush.…
Convert Numbers to Text in Excel
By default, numbers are right-aligned and text is left-aligned in Excel. This example teaches you how to convert numbers to ‘text strings that represent numbers’. 1. Select the range A1:A4 and change the number format to Text. 2. Precede a number by an apostrophe and it will also be treated as text. 3a. If you add text to a number and still want to format this…
Merge Cells in Excel
This page illustrates how to merge selected cells into one large cell. Why Merge Cells in Excel? This can be useful if you want to make clear that a label in Excel applies to multiple columns. Before you start: this feature only keeps the value in the upper-left cell and deletes all other values. Go here to concatenate (join) strings in Excel. 1. Enter a label in…
Strikethrough in Excel
Apply or remove strikethrough text formatting This example teaches you how to apply a strikethrough effect to text in Excel. In other words, learn how to draw a line through text in Excel. You can still read text with a strikethrough effect. 1. For example, select the range A2:A4. 2. Right click, and then click Format Cells (or press Ctrl + 1). The ‘Format…
Number Decimal Places in Excel
Learn how to show fewer decimal places in Excel without changing the number itself. You can also round a number. 1. Enter the value 2.175 into cell A1. 2. On the Home tab, in the Number group, click the Decrease Decimal button twice. Result: Note: if you look at the formula bar, you can see that cell A1 still contains the value 2.175. 3. We can…
Change Cell Styles in Excel
Quickly format a cell by choosing a cell style. Quickly format a range of cells by choosing a table style. You can also create your own cell style. 1. For example, select cell B2 below. 2. On the Home tab, in the Styles group, choose a cell style. Result. To create your own cell style, execute the following steps. 3. On the…
How to change Excel Workbook Themes
Excel workbooks come with free built in themes. The themes are used to change the look of your workbook with the click of a button. Each theme consists of 12 colors, two fonts (Headings and Body) and effects for shapes and SmartArt. Easy ways to format a worksheet 1. On the Page Layout tab, in the Themes group, you can see that the…
Wrap Text Automatically vs Manual Line Break in Excel
Wrap text in Excel if you want to display long text on multiple lines in a single cell. You can format the cell so the text wraps automatically, or enter a manual line break. Data in the cell wraps to fit the column width, so if you change the column width, data wrapping adjusts automatically. Wrap Text Automatically 1. For example, take…
Superscript and Subscript in Excel
Superscript or Subscript generally is a number, figure, symbol, or indicator that is smaller than the normal line of type and is set slightly below it (subscript) or above it (superscript). It’s easy to format a character as superscript or subscript in Excel. 1. For example, double click cell A1. 2. Select the value 2. 3. Right click, and then click Format Cells (or press Ctrl +…
How To Create To Do List Using Fancy Check Mark in Excel
To insert a check mark symbol in Excel, simply press SHIFT + P and use the Wingdings 2 font. You can also create a check list that uses check boxes. 1. Select cell A1 and press SHIFT + P to insert a capital P. 2. On the Home tab, in the Font group, select the Wingdings 2 font. To insert a fancy check mark, change…
Quickly Highlight Cells Containing Different Content in a Row
This example teaches you how to quickly highlight cells whose contents are different from the comparison cell in each row. 1. To select the range A1:C15, click on cell A1 and drag it to cell C15. Note: because we selected the range A1:C15 by clicking on cell A1 first, cell A1 is the active cell (Use ENTER and TAB to…
Advanced Number Formats in Excel
Excel has many built-in formats. You can select standard number formats such as General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, Fraction, Scientific, Text on the home tab of the ribbon using the Number Format menu. If you cannot find the right format, you can create a custom number format. N/B: A custom number format is stored in the…
Format Cells and Numbers in Excel
Format Cells simply refers to style; font, font colour, font size, borders and more while Format Numbers refer to data pattern, ie percentage, currency and more. Format Numbers When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). 1.…
Excel Custom Number Format with Leading Zeros, Add Text, Colors Example
Beyond the built-in options: custom formatting The Format Cells dialog offers so many options available “out-of-the-box.” The available format category and type options go far beyond the basics. But, if what is available still doesn’t meet your requirements, you can always create a custom format. Let’s consider a set of numbers. We start by opening the Format Cells dialog box as we did…