Microsoft Excel and Excel for the Web Formulas, Tables, Formatting and Dates
View All Tags
To add up only the rows dated within a range, give SUMIFS two conditions on the same date column: on or after the start, ">="&start, and before the end, "<"&end. The comparison sign goes in quotes and & joins it to the cell with the date.
A log of shifts answers "who worked when"; payroll asks "how many hours did each person do each month?" One SUMIFS formula, copied across a grid of names and months, answers it: it adds up Hours where the Employee matches the row and the Date falls in the column's month.
When you copy a formula, Excel moves its references with it: A2 copied one row down becomes A3. A $ stops that. $C$7 never moves, $A2 keeps its column but moves its row, and B$1 keeps its row but moves its column. Mixed references let one formula fill a whole grid.
EDATE moves a date by a number of whole months: =EDATE(B1,1) is one month after B1. If B1 is the first of a month, the result is the first of the next month. That's how a row of month headings can build itself from the first one.
A drop-down list makes people pick from set choices instead of typing, so the same department is never spelt two ways. In Excel for the web it's a Data Validation rule: Data → Data Validation → Allow: List, with the choices typed in Source.
New sheets are called Sheet1, Sheet2 and so on. A descriptive name makes the workbook easier to find your way around and makes formulas that mention the sheet readable. Right-click the tab, select Rename, type, and press Enter.
A workbook can hold several sheets, each with its own grid: data on one, totals on another. In Excel for the web, the + button next to the sheet tabs at the bottom of the page adds one.
For month headings, put the first day of each month in the cell and give it the custom format mmm yyyy. 01/10/2026 shows as Oct 2026, and the cell is still a real date, so formulas can compare other dates with it.
Behind every date in Excel is a number: how many days it is since 1 January 1900. 1 October 2026 is 46296. You can see it by giving the cell the General format. Knowing this explains why you can subtract dates, add months, and why a date can suddenly show as a five-digit number.
When none of the Number Format options fit, write the format yourself as a format code: mmm yyyy for Oct 2026, [h]:mm for 60:15, dd/mm/yyyy hh:mm for a date and time. In Excel for the web, codes go in the All formatting options pane: Number Format → More Number Formats → Custom.
