This question is one that I have quite often when I do data analysis at my job :
I am very often working with data concerning time of arrival / time of departure of patients.
I want to do bar graph of the number of patients actually there during each one hour period.
Exemple :
Patient A arrival 8 am departure 12 am
Patient B arrival 10 am departure 2pm
I want a bar value 1 for 8-9, then a bar value of 1 for 9-10 then a bar value of 2 for 10-11 and a bar value of 2 for 11-12 then a bar value of 1 for 12am-1pm and bar value of 1 for 1pm-2pm.
The only way that I have managed to do that so far is to create a column for each time slot and then for each patient (each line on my excel table) with a long 'if, if if.. formula' depending of course on the arrival time / departure time, plug 1 if patient is there during the time slot or nothing if he was not there, and then do a chart with this raw data.
I was wondering if there was any other way, if there is an excel time function which I could use, specifying the time slot and time of arrival/departure which could directly give '1' if patient was there ' if he was not. or a graph who could directly do that without the 24 column needed to chart a day's worth of activity.
Thank you for your help, any feedback/advice would help me a lot.
Christiane
Auto Fill is a great way to add data such as months, dates, or repetitive information. There are also some useful tricks like adding a series of dates and le. Suppose we have a situation in which the time would be taken from the next 15 min slot for example if the activity starts at 10:10 the effective time would be from 10:15. The effective timings will be every 15 min, e.g. 10, 10:15, 10:30, 10:45 and so on. STEPS TO CONVERT THE TIME INTO SLOTS. Select the cell where the slot time is to be decided.
Inputting Excel Functions
The Excel Autofill feature can be used to populate a range of cells with either a repeat value, a series of values, or just a cell format.
To use the simple Excel Autofill:
- Enter a value into the start cell;
- Use the mouse to drag the 'fill handle' (the small black square at the bottom right of the start cell) across the range of cells to be filled;
- When you drag the 'fill handle' across the range of cells to be filled, Excel will fill the selected cells, by either repeating the value in the first cell or by inserting a sequence from the first cell value (e.g. 1, 2, 3, ..);
- Click on the 'Auto Fill Options' box, which will appear at the end of your selected range of cells. This will give you the following different options:
- Copy Cells - copy the initial cell across the selected range;
- Fill Series - fill the selected range with a series of values (typically incrementing by 1), starting with the initial cell value;
- Fill Formatting Only - fill the selected range with the formatting, but not the values of the initial cell;
- Fill Without Formatting - fill the selected range with values, but do not copy the formatting from the initial cell.
Select the option that you required for the filled cells.
Autofill Using More Than One Starting Cell Value
If you want Excel to recognise a series that is not a simple increment by 1, this can be done by typing the first two values of your series into the first and second cells of a range. Select both of these cells and again, drag the fill handle across the range to be filled. Excel will automatically recognise the pattern from the two initial cells and continue this across the selected range. Using this method, you can get Excel to fill cells by increments or decrements of any number (e.g. 2, 4, 6, 8, ..).
Alternatively, if you want Excel to fill cells with repeated alternating values (e.g. 1, 2, 1, 2, 1, 2, ..) you can start off the pattern in the first two (or more) cells, then, with the initial cells highlighted, drag the fill handle and then click on the 'Auto Fill Options' box. Within this box, select the option 'Copy Cells' to repeat the initial cell values across the selected range.
Autofill Dates & Times
As dates and times are stored in Excel as numbers, these can also be used with the Excel Autofill.
Excel Auto Fill From List
By default, if you just type in a single date or time and drag the fill handle, dates and times will complete in a series, by adding one day (for dates), or one hour (for times). However, as with simple numbers, you have the option of clicking on the 'Auto Fill Options' box, to select a different type of Auto fill.
Times have the same four Auto fill options as are shown above, for simple numbers (i.e. Copy Cells, Fill Series,Fill Formatting Only, Fill Without Formatting). However, for dates, there are additional Auto Fill options. As well as the four options for simple numbers and times, there are also the following:
- Fill Days - Look for a pattern in the day when filling the selected cells;
- Fill Weekdays - Look for a pattern in the day when filling the selected cells, but do not include Saturdays or Sundays in the series;
- Fill Months - Look for a pattern in the month when filling the selected cells;
- Fill Years - Look for a pattern in the year when filling the selected cells.
How To Fill Down In Excel
Autofill Text Values
The Excel Autofill will generally fill a column with text values by repeating the value(s) in the first cell(s). However, there are some text values that Excel recognises as part of a series. These are:
| Weekdays (abbreviated or full names): |
| Months (abbreviated or full names): |
| Rank: |
| Other text containing numbers: |
Autofill Functions and Formulas
The Excel Autofill feature also works with functions and formulas in Excel. However, with this type of Autofill, there is no 'series fill' option. Instead, Excel observes the rules of Absolute and Relative Cell References (i.e. if a row or column reference is preceded by a $ sign, excel will keep the reference constant as the formula is copied to other cells; Otherwise, the row or column reference will be adjusted as the formula is copied to other cells.
Horizontal and Vertical Autofill
As well as working down a column, the Autofill feature also works horizontally, across rows.
Simply drag the fill handle across the cells that you want to populate.
Autofill Multiple Rows or Columns Simultaneously
The Excel Autofill can also handle data in more than one row or column. This is shown in the example below, in which cells A1 and A2 have numeric values 1 and 2, and cells B1 and B2 both have the numeric value 3.
Highlighting cells A1 to B2, and then dragging the fill handle down columns A and B causes the Autofill to complete both columns with their own series (i.e. column A completes with 1, 2, 3, 4, .., and column B completes with 3, 3, 3, 3, ..)
Double Click on the Fill Handle
For speed, you can Autofill a column by double-clicking on the fill handle of a highlighted cell (or range of cells). If the cells below or adjacent to the highlighted cell (or range) contain values, double clicking the fill handle causes the Autofill to fill down the current column until it reaches the end of the current data range.
Excel Auto Fill From List
By default, if you just type in a single date or time and drag the fill handle, dates and times will complete in a series, by adding one day (for dates), or one hour (for times). However, as with simple numbers, you have the option of clicking on the 'Auto Fill Options' box, to select a different type of Auto fill.
Times have the same four Auto fill options as are shown above, for simple numbers (i.e. Copy Cells, Fill Series,Fill Formatting Only, Fill Without Formatting). However, for dates, there are additional Auto Fill options. As well as the four options for simple numbers and times, there are also the following:
- Fill Days - Look for a pattern in the day when filling the selected cells;
- Fill Weekdays - Look for a pattern in the day when filling the selected cells, but do not include Saturdays or Sundays in the series;
- Fill Months - Look for a pattern in the month when filling the selected cells;
- Fill Years - Look for a pattern in the year when filling the selected cells.
How To Fill Down In Excel
Autofill Text Values
The Excel Autofill will generally fill a column with text values by repeating the value(s) in the first cell(s). However, there are some text values that Excel recognises as part of a series. These are:
| Weekdays (abbreviated or full names): |
| Months (abbreviated or full names): |
| Rank: |
| Other text containing numbers: |
Autofill Functions and Formulas
The Excel Autofill feature also works with functions and formulas in Excel. However, with this type of Autofill, there is no 'series fill' option. Instead, Excel observes the rules of Absolute and Relative Cell References (i.e. if a row or column reference is preceded by a $ sign, excel will keep the reference constant as the formula is copied to other cells; Otherwise, the row or column reference will be adjusted as the formula is copied to other cells.
Horizontal and Vertical Autofill
As well as working down a column, the Autofill feature also works horizontally, across rows.
Simply drag the fill handle across the cells that you want to populate.
Autofill Multiple Rows or Columns Simultaneously
The Excel Autofill can also handle data in more than one row or column. This is shown in the example below, in which cells A1 and A2 have numeric values 1 and 2, and cells B1 and B2 both have the numeric value 3.
Highlighting cells A1 to B2, and then dragging the fill handle down columns A and B causes the Autofill to complete both columns with their own series (i.e. column A completes with 1, 2, 3, 4, .., and column B completes with 3, 3, 3, 3, ..)
Double Click on the Fill Handle
For speed, you can Autofill a column by double-clicking on the fill handle of a highlighted cell (or range of cells). If the cells below or adjacent to the highlighted cell (or range) contain values, double clicking the fill handle causes the Autofill to fill down the current column until it reaches the end of the current data range.
A video on the Excel Autofill is provided on the Microsoft Office Support website.
Return to the ExcelFunctions.net Home Page
