Excel Beginners

Data Types and Formatting: Step 2 To Mastering Spreadsheets

It Doesn’t always add up like it should

Numerical Values

What Is A Numerical Value?

A numerical value is any piece of data which can be manipulated by any form of mathematics. This can include traditional number values such as integers, and decimals, as well as dollar values, dates, times, and percentages, among others.

How Do Numerical Values Work?

Numerical values work in much the same way as you would expect them to. What is important to understand is that date, time, and duration values are not treated as many people would expect, and they have specific rules which must be followed. For all other numerical values, they operate according to the same principles which are taught in school. Order of operations are followed, and other logical rules apply.

Special rules for date, time, and duration values are as follows!

Dates are formatted to appear as dates but are represented by whole numbers (integers). The date which corresponds to the number ‘0’ is January 1st, 1900. You can test this by entering the number 0 into a cell, and changing the cell format to ‘Date’.

Time values are a decimal value between ‘0’ and ‘0.99999’, which represent midnight and 23:59:59 respectively. They are always attached to a date value, even if that date represents January 1st, 1900.

Duration is not something which you will find listed in an Excel menu for formatting. Rather it is the knowledge that you can use a time format which does not specify AM/PM in order to simulate time passed between two periods.

The last point with time values is that to get the full number value of the decimal representation you must multiply the number by 24. This is because excel uses the number ‘1’ to measure a full day, and a 24th of that represents one hour.

What Are Numerical Values Used For?

These values are what users will primarily use in Excel. They will encompass the majority of data which gets passed to or from Excel into other systems. Whether for reporting, analysis, dashboards, or storage, numbers are used for every aspect of Excel. Even when the values you are working with are text values, a simple analysis will use math and numbers to describe the data set.