ݺߣ

ݺߣShare a Scribd company logo

If only there was an easier way….of creating a series

•
•
Thales Training & Consultancy
Thales Training & Consultancy

The document discusses how Excel can automatically generate series of dates, numbers, days of the week, and custom lists through its autofill and series generation features. It explains that autofill looks for patterns to continue sequences and can do consecutive or non-consecutive items. The document also describes how to create custom lists that can generate unique sequences and provides an example of generating every Friday between two dates.

1 of 2
Download to read offline
If only there was an easier way….of creating a series I am sure that on numerous occasions you have had to prepare long lists in Excel, and no doubt some of these involved lists of dates, members of staff, office sites etc. I bet you spent ages manually typing it all out the first time and have done so several times since, but fear ye not….Excel has a few neat tricks that can help with all of this, from days of the week through to the name of every employee in your company! Discovering that Excel is clever enough to complete the days of the week for you when you type Monday into a cell and then click and drag it, can be quite a revelation. Being able to set up custom lists is another and you can also get Excel to complete whole series of dates and numbers for you. If anything in the previous few lines was in fact a revelation to you, then read on. The ‘autofill’ that most people are aware of is days of the week, or months of the year. No need to manually type all these out. Simply type your start day or month, go to the bottom right hand corner of the cell until you see the pointer change into the cross-hairs, then click and drag down or to the right and Excel will continue the series for you. If you want to do alternate days, enter Monday in the first cell, Wednesday in the next cell, highlight BOTH cells and click and drag as described above. This time Excel will see that you have non- consecutive days and will continue the series accordingly. But where does all this come from? Is Excel really that clever? Well…not really. This is all based on some pre-set lists stored in CUSTOM LISTS. To find these go to EXCEL OPTIONS, ADVANCED tab, GENERAL and click on EDIT CUSTOM LISTS. Now here comes the clever bit…..you can add your own custom lists so that when you enter the first name (for example), you can use the click and drag technique to make Excel finish the list for you. Like that if you have a list you don’t want to have sorted in alphabetical order, you can create your own list order. If you create the list as above, make sure you have a comma between each value. Once you have manually entered the list make sure you click on ADD to save the list. You also have the option to import a whole list from Excel if you have a ready-made list to hand. Using the click and drag technique to complete a series sometimes causes confusion. With the days of the week, a simple click and drag does the job, but if you have a number, instead of creating a Written by: Richard Harker, Business Systems Training Consultant Contact us: thales-trainingconsultancy.com twitter.com/thalestraining 0800 163 469 http://blog.thales-trainingconsultancy.com
series it copies the value! To get around this simply press and hold down Ctrl when you click and drag the cross-hair pointer. If you use Ctrl with days of the week, for example, then it copies it! Now that we know how to do some basic sequential and non-sequential series, what if we want something a little more involved? Take dates for example. I might only want to display workdays and not weekends, or perhaps every Friday for the next few months. I could set up a series showing every single day between two dates and then remove all the dates corresponding to Sat/Sun but that could take a long time and is prone to errors. On your HOME tab there is small icon that gives you all the options to create series of dates, numbers etc. Type in your first Friday date: 01/07/2011 Staying in the same cell, click on the FILL icon and select SERIES. Select the direction you want your series to go in – across in ROWS or down in COLUMNS. Select DATE as this is what we want. Then as we only want a WEEKDAY select that, then tell Excel how many units (in this case weekdays) you want it to step by. As we want to only show every Friday we tell it to use steps of 5 i.e. every 5th weekday. The final step is to give it a STOP VALUE. Here, I have set it to the end of August. Click on OK and Excel will fill in all the Fridays between the 1st of July and the 31st of August. Easy! If you want to double check that it has in fact given you Fridays and not any other day, highlight all your date cells, press Ctrl + 1 to open the format cells dialog box, select custom and type in dddd, now instead of the date showing you should see the word Friday in every cell. So if you need to write out lists on a regular basis or set up date or number sequences etc don’t waste time doing it all manually, use Excel’s tools to do it for you! Written by: Richard Harker, Business Systems Training Consultant Contact us: thales-trainingconsultancy.com twitter.com/thalestraining 0800 163 469 http://blog.thales-trainingconsultancy.com

More Related Content

If only there was an easier way….of creating a series

  • 1. If only there was an easier way….of creating a series I am sure that on numerous occasions you have had to prepare long lists in Excel, and no doubt some of these involved lists of dates, members of staff, office sites etc. I bet you spent ages manually typing it all out the first time and have done so several times since, but fear ye not….Excel has a few neat tricks that can help with all of this, from days of the week through to the name of every employee in your company! Discovering that Excel is clever enough to complete the days of the week for you when you type Monday into a cell and then click and drag it, can be quite a revelation. Being able to set up custom lists is another and you can also get Excel to complete whole series of dates and numbers for you. If anything in the previous few lines was in fact a revelation to you, then read on. The ‘autofill’ that most people are aware of is days of the week, or months of the year. No need to manually type all these out. Simply type your start day or month, go to the bottom right hand corner of the cell until you see the pointer change into the cross-hairs, then click and drag down or to the right and Excel will continue the series for you. If you want to do alternate days, enter Monday in the first cell, Wednesday in the next cell, highlight BOTH cells and click and drag as described above. This time Excel will see that you have non- consecutive days and will continue the series accordingly. But where does all this come from? Is Excel really that clever? Well…not really. This is all based on some pre-set lists stored in CUSTOM LISTS. To find these go to EXCEL OPTIONS, ADVANCED tab, GENERAL and click on EDIT CUSTOM LISTS. Now here comes the clever bit…..you can add your own custom lists so that when you enter the first name (for example), you can use the click and drag technique to make Excel finish the list for you. Like that if you have a list you don’t want to have sorted in alphabetical order, you can create your own list order. If you create the list as above, make sure you have a comma between each value. Once you have manually entered the list make sure you click on ADD to save the list. You also have the option to import a whole list from Excel if you have a ready-made list to hand. Using the click and drag technique to complete a series sometimes causes confusion. With the days of the week, a simple click and drag does the job, but if you have a number, instead of creating a Written by: Richard Harker, Business Systems Training Consultant Contact us: thales-trainingconsultancy.com twitter.com/thalestraining 0800 163 469 http://blog.thales-trainingconsultancy.com
  • 2. series it copies the value! To get around this simply press and hold down Ctrl when you click and drag the cross-hair pointer. If you use Ctrl with days of the week, for example, then it copies it! Now that we know how to do some basic sequential and non-sequential series, what if we want something a little more involved? Take dates for example. I might only want to display workdays and not weekends, or perhaps every Friday for the next few months. I could set up a series showing every single day between two dates and then remove all the dates corresponding to Sat/Sun but that could take a long time and is prone to errors. On your HOME tab there is small icon that gives you all the options to create series of dates, numbers etc. Type in your first Friday date: 01/07/2011 Staying in the same cell, click on the FILL icon and select SERIES. Select the direction you want your series to go in – across in ROWS or down in COLUMNS. Select DATE as this is what we want. Then as we only want a WEEKDAY select that, then tell Excel how many units (in this case weekdays) you want it to step by. As we want to only show every Friday we tell it to use steps of 5 i.e. every 5th weekday. The final step is to give it a STOP VALUE. Here, I have set it to the end of August. Click on OK and Excel will fill in all the Fridays between the 1st of July and the 31st of August. Easy! If you want to double check that it has in fact given you Fridays and not any other day, highlight all your date cells, press Ctrl + 1 to open the format cells dialog box, select custom and type in dddd, now instead of the date showing you should see the word Friday in every cell. So if you need to write out lists on a regular basis or set up date or number sequences etc don’t waste time doing it all manually, use Excel’s tools to do it for you! Written by: Richard Harker, Business Systems Training Consultant Contact us: thales-trainingconsultancy.com twitter.com/thalestraining 0800 163 469 http://blog.thales-trainingconsultancy.com