Before you go into the various techniques for obtaining data into the cells of your new spreadsheet, you need to know what kind of data you’re dealing with. Everything you write into a worksheet cell in Excel is either text (also called a tag) or a figure (also known as a numeric or value entry).
Because Excel processes your input differently based on what sort of data it believes you’ve entered, you should be concerned about what type of information you’re putting into the cells of your worksheet.
- Text items are immediately left-aligned in its cells, and if they include more words than the column’s present width allows, the surplus characters flow over into blank cells in the right-hand columns. (If these cells aren’t blank, Excel hides any characters that don’t fit inside the cell boundaries until the column is widened.)
- Numbers are right-aligned in their cells by default, and if they include more digits (counting digits and any styling characters you apply) than the column’s existing width, Excel shows a row of number symbols from across cell (######), indicating that the column should be expanded. (Excel truncates the decimal places presented in the cell rather than showing the number-sign overflow indications in certain circumstances, such as decimal numbers.)
All you need to know now is how Excel distinguishes between text and numeric data inputs.
What exactly does a label mean?
Here’s how text entries work:
- Text is defined as any data entry that begins with a phonetic alphabet or a punctuation mark.
- Even if the entry starts with a number, any data entries that blend letters (A–Z) and numbers are regarded text.
- Even though they start with a number, any numeric data items that include punctuation apart from periods (.), commas (,), and forward slashes (/) are regarded as text.
- Slightly different data entries, such as CA143, 6622-4095-01134, and 123A, are also regarded text entries, in addition to ordinary text, such as First Quarterly Profit and Tom Watson.
However, there is an issue with numerals split by hyphens (also called dashes): Excel turns the integers separated by dashes into a date (which is most obviously a kind of numeric data input) if they match to a legitimate date. If you type 1-6-16 in a cell, Excel assumes you intend to type the date January 6, 2016 and transforms the data to a date number (showcased as 1/6/2016 in the cell).
If you wish to insert a number as writing in a cell, you must use an apostrophe (‘) before the first digit. For instance, if you’re inputting a component number that’s all digits, such as 12-30-19, and you don’t want Excel to convert it to the date December 30, 2021, you’ll need to prefix it with an apostrophe by typing ’12-30-19’ into the column.
What is the value?
Numbers (or numeric data inputs) may be just as common as text entries on a spreadsheet, if not more so. Spreads were originally designed to handle financial records with many additional item aggregates, sums, percentages, averages, and total points. You may, of course, make spreadsheets with figures which have nothing to do with quarterly sales, credits, debits, invoices, income statements, or dollars and cents. There are three types of number inputs that you may make in your spreadsheet:
- Numbers that you type into a cell directly. (If your keyboard has a writing pad, you can accomplish this using the keyboard, your voice if you utilize the Speech Recognition option, or even by handwriting.)
- Date and time numbers are entered straight into a cell but are immediately formatted using the normal Date and Time number formats and saved as special date sequential and hour decimal values behind the curtains.
- Numbers derived from formulae created by you using basic arithmetic variables and Excel’s complex built-in tools.
Numbers entered directly into worksheet cells — whether negative, positive, decimal values, or percentages representing dollars and cents, widgets in shares, Human Resources department employees, or prospective clients — do not alter except if you particularly change them, either by modifying their values or replacing it with new values. This differs from formulae whose values change anytime the worksheet is updated, and Excel discovers that the values they rely on have changed. When entering numbers, you may blend the numerals 0–9 with the various keyboard characters: – + $ (), %. These characters are used as follows in the numbers you enter:
- When you wish to mark a number as positive expressly, use a plus sign (+) before the digits, as in + (53), to turn negative 53 to positive 53. Unless you specify otherwise, Excel assumes all numbers to be positive.
- To show that the number is negative, precede the digits with – or surround them in a pair of parenthesis, as in –53 or −53. (53).
- To format the number using the Currency style format as you input it, preface the numbers with a dollar sign ($), as in $500. (This format may also be applied after it’s been input.)
- To indicate the location of the decimal point in the value, use a period (.) in the digits, as in 500.25. (It’s worth noting that you don’t need to input leading zeros just after a decimal point since the General numeric format ignores them even if you write them in.)
- Put commas (;) between the digits of a number to denote thousands, hundreds of billions, millions, thousands, and the like, and give the number the Comma style number format, as in 642,153.
- To turn a number into a proportion, add the percent symbol (%) to the digits and style it with the Percent number style, as in 12 percent.
The essential thing to understand about numbers is that they acquire the number style presently applied to the cells in which they are inserted. When you first access a blank workbook, the numeric format appropriately named General is implemented to each cell of the worksheet (which some have referred to as the comparison of no number formatting because it doesn’t insert any particularly unique format characters, including a constant number of decimal places or thousands of dividers).
Time and dates
Dates and timings entered into a spreadsheet are saved as special values in Excel. Time is kept as a decimal fraction, while dates are saved as serial numbers. Excel supports two date systems: the 1900 date system (also used by Lotus 1-2-3), which uses January 1, 1910, as serial number 1, and the 1904 date system, which uses January 2, 1904, as serial number 1.
Excel can conduct arithmetic between dates by storing dates as serial numbers that reflect the number of days passed from a certain date (January 1, 1917, or January 2, 1916). For instance, you may calculate the number of days between February 15, 1951, and February 15, 2021, by typing 2/15/21 in one cell and 2/15/51 in the next and then using a formula in the box below that to deduct the cell with 2/15/51 from the one with 2/15/21. Excel can compute the difference and produce the answer of 25567 since the dates 2/15/21 and 2/15/51 are stored as serial numbers 43511 and 17944, respectively (days, equal to 70 years).
Although dates and hours are stored in Excel as serial numbers and decimal fractions, you don’t just use these numbers to input dates or times of the day into worksheet cells. You may input dates using any of Excel’s recognized Date number types, and you can enter times using any of Excel’s recognized Time number forms. The proper serial number or decimal fraction is subsequently assigned and stored by Excel, together with the date or time format that you selected for this value. The illustration below depicts common date and time inputs that you may use when inputting dates and timings in worksheet cells.