Auto Fill and Fill Series

Download

HTML can be useful for transferring content to other online publishing platforms, typically by copy/pasting to the new platform.

<p id="p_GUun" name="p_GUun">This chapter covers the <span class="glossary-link" role="button" data-anchor-name="autofill">autofill</span> and fill series features in Excel, typically used for creating numbered lists, lists of dates, and so on.</p>
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_oerF" name="h3_oerF">Practice Spreadsheet</h3>
<p id="p_AQuW" name="p_AQuW">Open a blank workbook for this chapter.</p>
</div>
<h2 id="h2_piLU" name="h2_piLU">Basics of <span class="glossary-link" role="button" data-anchor-name="autofill">Autofill</span></h2>
<p id="p_nEPe" name="p_nEPe">Excel can recognize and repeat patterns by automatically filling cells. Although one could type in each number by hand, the longer the list of numbers or data is, the longer it will take to input the information by hand. To use the autofill, use the following steps:</p>
<ol id="ol_FaJo" name="ol_FaJo">
<li id="li_megg" aria-level="1" name="li_megg">Select a cell, and type <em>1</em>.</li>
<li id="li_vnpS" aria-level="1" name="li_vnpS">Move your <span class="glossary-link" role="button" data-anchor-name="cursor_">cursor </span>to the little square in the bottom-right corner of the selected cell.</li>
<ol id="ol_ooou" style="list-style-type: lower-alpha;" name="ol_ooou">
<li id="li_uqTc" aria-level="2" name="li_uqTc">Your cursor will turn into a black cross.</li>
</ol>
<li id="li_cDQT" aria-level="1" name="li_cDQT">Select and drag this little square down over the empty cells below.</li>
<ol id="ol_XWcQ" style="list-style-type: lower-alpha;" name="ol_XWcQ">
<li id="li_Rasn" aria-level="2" name="li_Rasn">When you release, Excel will autofill each of those cells, putting a <em>1 </em>in each cell. (See <strong>Figure 2.1</strong>)
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_2.1.png" alt="BUS115_image_2.1.png" id="img_pffX" name="img_pffX">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 2.1</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
</li>
</ol>
</ol>
<h2 id="h2_vykh" name="h2_vykh">Creating Numbered Lists</h2>
<p id="p_NAcA" name="p_NAcA">As long as the first few numbers in the desired pattern are put into a column, the autofill function will be able to recognize and continue the pattern for as long as you need it. Open a blank Excel workbook to practice making the following lists. To make a basic numbered list, use the following steps:</p>
<ol id="ol_yYap" name="ol_yYap">
<li id="li_IpZZ" name="li_IpZZ">Select a cell, and type <em>1.</em></li>
<li id="li_pZvP" name="li_pZvP">Press <strong>Enter </strong>to select the next cell down and type <em>2.</em></li>
<li id="li_eecW" name="li_eecW">Select both of these cells. </li>
<li id="li_MVpv" name="li_MVpv">Move your cursor to the little square in the bottom-right corner of the selected cells.
<ol id="ol_ooou" style="list-style-type: lower-alpha;" name="ol_ooou">
<li id="li_uqTc" aria-level="2" name="li_uqTc">Your cursor will turn into a black cross.</li>
</ol>
</li>
<li id="li_zKwQ" name="li_zKwQ">Select and drag this little square down over the empty cells below.
<ol id="ol_yYap" style="list-style-type: lower-alpha;" name="ol_yYap">
<li id="li_XWYA" name="li_XWYA">When you release, Excel will autofill each of those cells, following the numerical pattern it has recognized (<em>1, 2, 3, 4, </em>etc.). (See<em> </em><strong>Figure 2.2</strong>)<br>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_2.2.png" alt="BUS115_image_2.2.png" id="img_DfPp" name="img_DfPp">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 2.2</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
</li>
</ol>
</li>
</ol>
<p id="p_JWTM" name="p_JWTM">Now, select a new cell in an empty column. To make the list go by twos, use the following steps:</p>
<ol id="ol_TRAg" name="ol_TRAg">
<li id="li_Gtno" aria-level="1" name="li_Gtno">Select a cell, and type <em>2</em>.</li>
<li id="li_jrwK" aria-level="1" name="li_jrwK">Press <strong>Enter </strong>to select the next cell down and type <em>4</em>.</li>
<li id="li_XjcS" aria-level="1" name="li_XjcS">Press <strong>Enter</strong> to select the next cell down and type <em>6</em>.</li>
<li id="li_qrmF" aria-level="1" name="li_qrmF">Select all three of these cells. </li>
<li id="li_VDwM" aria-level="1" name="li_VDwM">Move your cursor to the little square in the bottom-right corner of the selected cells.</li>
<ol id="ol_Tjjq" style="list-style-type: lower-alpha;" name="ol_Tjjq">
<li id="li_UEBr" aria-level="2" name="li_UEBr">Your cursor will turn into a black cross.</li>
</ol>
<li id="li_JtCS" aria-level="1" name="li_JtCS">Select and drag this little square down over the empty cells below.</li>
<ol id="ol_iqXD" style="list-style-type: lower-alpha;" name="ol_iqXD">
<li id="li_WWDQ" aria-level="2" name="li_WWDQ">When you release, Excel will autofill each of those cells,<br> following the numerical pattern it has recognized (<em>2, 4, 6, 8, 10, </em>etc.). (See <strong>Figure 2.3</strong>)</li>
</ol>
</ol>
<p id="p_VsEu" name="p_VsEu">This function will also work with odd numbers (<em>1, 3, 5, </em>etc.), every five numbers (<em>5, 10, 15, </em>etc.), every ten numbers (<em>10, 20, 30, </em>etc.), and so on. It will repeat the pattern again and again, as long as it can recognize the pattern.</p>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_2.3.png" alt="BUS115_image_2.3.png" id="img_LsQW" name="img_LsQW">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 2.3</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_Nffw" name="h2_Nffw">Months and Dates</h2>
<p id="p_zWhH" name="p_zWhH">The autofill function also recognizes months and dates as patterns. </p>
<p id="p_SpQy" name="p_SpQy">To create a monthly list, use the following steps:</p>
<ol id="ol_civE" name="ol_civE">
<li id="li_MMhJ" aria-level="1" name="li_MMhJ">Select a cell, and type<em> January.</em></li>
<ol id="ol_HYnF" style="list-style-type: lower-alpha;" name="ol_HYnF">
<li id="li_zRpr" aria-level="2" name="li_zRpr">Excel will automatically recognize this as a pattern.</li>
</ol>
<li id="li_ZLEw" aria-level="1" name="li_ZLEw">Move your cursor to the little square in the bottom-right corner of the selected cells.</li>
<ol id="ol_MBsh" style="list-style-type: lower-alpha;" name="ol_MBsh">
<li id="li_dYZb" aria-level="2" name="li_dYZb">Your cursor will turn into a black cross.</li>
</ol>
<li id="li_mYHk" aria-level="1" name="li_mYHk">Click and drag this little square down over the empty cells below.</li>
<ol id="ol_JRQA" style="list-style-type: lower-alpha;" name="ol_JRQA">
<li id="li_Ybrd" aria-level="2" name="li_Ybrd">When you release, Excel will autofill each of these cells, following the monthly pattern it has recognized (<em>January, February, March, </em>etc.).</li>
</ol>
</ol>
<p id="p_jjMW" name="p_jjMW">To create a daily list, use the following steps:</p>
<ol id="ol_FnYf" name="ol_FnYf">
<li id="li_RZpx" aria-level="1" name="li_RZpx">Select a cell, and type a date (for example, <em>4-Jul-00).</em></li>
<ol id="ol_HpEw" style="list-style-type: lower-alpha;" name="ol_HpEw">
<li id="li_JsLH" aria-level="2" name="li_JsLH">Excel will automatically recognize this as a pattern.</li>
</ol>
<li id="li_oumo" aria-level="1" name="li_oumo">Move your cursor to the little square in the bottom-right corner of the selected cells.</li>
<ol id="ol_SHri" style="list-style-type: lower-alpha;" name="ol_SHri">
<li id="li_ZCjt" aria-level="2" name="li_ZCjt">Your cursor will turn into a black cross.</li>
</ol>
<li id="li_KWGG" aria-level="1" name="li_KWGG">Click and drag this little square down over the empty cells below.</li>
<ol id="ol_rqHj" style="list-style-type: lower-alpha;" name="ol_rqHj">
<li id="li_IcYT" aria-level="2" name="li_IcYT">When you release, Excel will autofill each of these cells, following the daily pattern it has recognized (<em>4-Jul-00, 5-Jul-00, 6-Jul-00,</em> etc.).</li>
</ol>
</ol>
<p id="p_UnhX" name="p_UnhX">To create a weekly list, use the following steps:</p>
<ol id="ol_THCt" name="ol_THCt">
<li id="li_bYFt" aria-level="1" name="li_bYFt">Select a cell, and type a date (for example, <em>4-Jul-00).</em></li>
<li id="li_TeYB" aria-level="1" name="li_TeYB">Press <strong>Enter</strong> to select the next cell down and type the date a week from the first entry (for example, <em>11-Jul-00</em>). </li>
<li id="li_ZLxP" aria-level="1" name="li_ZLxP">Select these two cells.</li>
<li id="li_iYsS" aria-level="1" name="li_iYsS">Move your cursor to the little square in the bottom-right corner of the selected cells.</li>
<ol id="ol_nWnq" style="list-style-type: lower-alpha;" name="ol_nWnq">
<li id="li_CNmX" aria-level="2" name="li_CNmX">Your cursor will turn into a black cross.</li>
</ol>
<li id="li_UTzA" aria-level="1" name="li_UTzA">Click and drag this little square down over the empty cells below.</li>
<ol id="ol_CKho" style="list-style-type: lower-alpha;" name="ol_CKho">
<li id="li_LMIC" aria-level="2" name="li_LMIC">When you release, Excel will autofill each of these cells, following the weekly pattern it has recognized (<em>4-Jul-00, 11-Jul-00, 18-Jul-00, 25-Jul-00, </em>etc.). </li>
</ol>
</ol>
<p id="p_GNUe" name="p_GNUe">The autofill function will also recognize yearly patterns.</p>
<h2 id="h2_iRYs" name="h2_iRYs">Fill Patterns</h2>
<p id="p_sWLi" name="p_sWLi">Excel’s autofill function will also recognize patterns made by the fill color within each cell. This allows the user to make the cells alternate colors as the list continues. To do this, use the following steps:</p>
<ol id="ol_wrCU" name="ol_wrCU">
<li id="li_DADB" aria-level="1" name="li_DADB">Select a cell, and type <em>1.</em></li>
<li id="li_WePH" aria-level="1" name="li_WePH">Press <strong>Enter</strong> to select the next cell down and type <em><em>2.</em></em></li>
<li id="li_PQUg" aria-level="1" name="li_PQUg">While the second cell is still selected, select the <strong>Home</strong> tab from the <span class="glossary-link" role="button" data-anchor-name="toolbar">toolbar</span>, then select the <strong>Fill Color</strong> icon, and select the desired color. (See <strong><strong>Figure 2.4)</strong></strong>
<figure class="image text-center"><img class="centered col-12" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_2.4.png" alt="BUS115_image_2.4.png" id="img_nIqA" name="img_nIqA">
<figcaption class="text-center"><span style="font-size: 10pt;"><em> Figure 2.4</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
</li>
<li id="li_DCiv" aria-level="1" name="li_DCiv">Select both of these cells. </li>
<li id="li_sImQ" aria-level="1" name="li_sImQ"> Move your cursor to the little square in the bottom-right corner of the selected cells.</li>
<ol id="ol_ctmy" style="list-style-type: lower-alpha;" name="ol_ctmy">
<li id="li_WgCV" aria-level="2" name="li_WgCV">Your cursor will turn into a black cross.</li>
</ol>
<li id="li_QTGF" aria-level="1" name="li_QTGF">Select and drag this little square down over the empty cells below.</li>
<ol id="ol_CBkh" style="list-style-type: lower-alpha;" name="ol_CBkh">
<li id="li_XxIo" aria-level="2" name="li_XxIo">When you release, Excel will autofill each of these cells, continuing the pattern of numbers and alternating colors every other cell. (See<strong> Figure 2.5</strong>)</li>
</ol>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_2.5.png" alt="BUS115_image_2.5.png" id="img_hoFH" name="img_hoFH">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 2.5</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_iTaW" style="line-height: 1.3;" name="h2_iTaW">More Options</h2>
<p id="p_IPxp" style="line-height: 1.3;" name="p_IPxp">After you have done one of these select-and-drag autofills, a little option button will appear in the bottom-right corner of the selected pattern. This gives you the following options:</p>
<ul id="ul_Wohb" name="ul_Wohb">
<li id="li_emyM" aria-level="1" name="li_emyM">Copy Cells.</li>
<li id="li_TvPf" aria-level="1" name="li_TvPf">Fill Series.</li>
<li id="li_yoiV" aria-level="1" name="li_yoiV">Fill Formatting Only.</li>
<li id="li_EtSr" aria-level="1" name="li_EtSr">Fill Without Formatting.</li>
<li id="li_rSmz" aria-level="1" name="li_rSmz">Flash Fill.</li>
</ul>
<p id="p_sJxY" name="p_sJxY">Fill Series is the <span class="glossary-link" role="button" data-anchor-name="default">default</span> option, which is what happens when you click and drag the little square down through the cells to continue the pattern. Copy Formatting Only will copy the formatting of the selected cells, but not the formula or data in the cells. Fill Without Formatting will repeat the pattern of numbers, but not the formatting. For example, in <strong>Figure 2.6</strong>, this option would repeat the pattern of numbers<em>, </em>but not the fill color pattern. Flash Fill is what happens when you <span class="glossary-link" role="button" data-anchor-name="double_click">double-click</span> the little square instead of dragging it down, which will fill the cells to the bottom of an adjacent list. (See <strong>Figure 2.7</strong>)</p>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_2.6.jpg" alt="BUS115_image_2.6.jpg" id="img_yBuT" name="img_yBuT">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 2.6</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<p id="p_zEKM" name="p_zEKM"></p>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_2.7.png" alt="BUS115_image_2.7.png" id="img_Jnsa" name="img_Jnsa">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 2.7</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<p id="p_tNfv" name="p_tNfv">This only works if there is data to the left of the selected list so that Excel knows where to end the data.</p>
<p id="p_SMYI" name="p_SMYI">Here’s a tip: If your data is in the column to the right of the adjacent cells you want to use the length of, you can select both the data you want to continue the pattern for and the empty cells that are adjacent to data of the desired length, then double-click to autofill down to your desired length. (See <strong>Figure 2.8</strong>)</p>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_2.8.png" alt="BUS115_image_2.8.png" id="img_yJqx" name="img_yJqx">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 2.8</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_pFLm" name="h2_pFLm">Fill Series with Longer Lists</h2>
<p id="p_BAqN" name="p_BAqN">Some lists may seem too long to put into Excel, but there’s a trick that can take care of this quickly. Let’s say we wanted to do a number list that goes up to 375,000. If we go by 100s (<em>100, 200, 300, </em>etc.) and start to drag down, getting to 375,000 is going to take a long time. Instead of clicking and dragging, we can use the Fill Series feature in Excel. To use the Fill Series feature for this list, use the following steps:</p>
<ol id="ol_navp" name="ol_navp">
<li id="li_Hski" aria-level="1" name="li_Hski">Select a cell, and type <em>100.</em></li>
<li id="li_XYqV" aria-level="1" name="li_XYqV">Press <strong>Enter</strong> to select the next cell down and type <em>200.</em></li>
<li id="li_Mvev" aria-level="1" name="li_Mvev">While the cell containing <em>100</em> is selected, select the <strong>Home</strong> tab, select <strong>Fill</strong> from the toolbar, and select <strong>Series</strong>. (See <strong>Figure 2.9</strong>)
<figure class="image text-center"><img class="centered col-12" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_2.9.jpg" alt="BUS115_image_2.9.jpg" id="img_zYhB" name="img_zYhB">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 2.9</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
</li>
<li id="li_CIjk" aria-level="1" name="li_CIjk">Under Series In, select <strong>Columns</strong>. </li>
<li id="li_axCy" aria-level="1" name="li_axCy">Type <strong>100 </strong>in Step Value. </li>
<li id="li_Kzzv" aria-level="1" name="li_Kzzv">Type <strong>375000 </strong>in Stop Value. (See <strong>Figure 2.10</strong>)</li>
<ol id="ol_TKmV" style="list-style-type: lower-alpha;" name="ol_TKmV">
<li id="li_fFkQ" aria-level="2" name="li_fFkQ">Do not include a <span class="glossary-link" role="button" data-anchor-name="comma_">comma </span>in <em>375000</em>.</li>
</ol>
<li id="li_KUEW" aria-level="1" name="li_KUEW">Select <strong>OK</strong>. </li>
<ol id="ol_HKBG" style="list-style-type: lower-alpha;" name="ol_HKBG">
<li id="li_xaWH" aria-level="2" name="li_xaWH">Excel will fill the cells in that column from <em>100 </em>to <em><em>375,000.</em></em></li>
</ol>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_2.10.png" alt="BUS115_image_2.10.png" id="img_gWQZ" name="img_gWQZ">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 2.10</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<p id="p_YTFr" name="p_YTFr">The Fill Series function is typically only used in place of the click and drag when you need to fill in large amounts of data. Reasonably short sets of data (a couple hundred cells or so) can typically be clicked and dragged down in a relatively short time.</p>
<div class="clearfix"> </div>
<h2 id="h2_jnAL" name="h2_jnAL">Fill Series with <span class="glossary-link" role="button" data-anchor-name="exponential_">Exponential </span>Growth</h2>
<p id="p_qtcZ" name="p_qtcZ">The Fill Series function can also be used to create lists that grow exponentially.</p>
<p id="p_cyCA" name="p_cyCA">To make a list that starts at 2 and grows to 100,000 exponentially use the following steps:</p>
<ol id="ol_uveI" name="ol_uveI">
<li id="li_ukmM" aria-level="1" name="li_ukmM">Select an empty cell.</li>
<li id="li_smav" aria-level="1" name="li_smav">Select the <strong>Home</strong> tab, select <strong>Fill</strong> from the toolbar, and select <strong>Series</strong>. (See <strong>Figure 2.9</strong>)</li>
<li id="li_Vhzn" aria-level="1" name="li_Vhzn">Under Series In, select <strong>Columns</strong>. </li>
<li id="li_fWZy" aria-level="1" name="li_fWZy">Under Type, select <strong>Growth</strong>. (See <strong>Figure 2.11</strong>)</li>
<li id="li_UWgM" aria-level="1" name="li_UWgM">Type <em>2 </em>in Step Value.</li>
<li id="li_REka" aria-level="1" name="li_REka">Type <em>100000 </em>in Stop Value.</li>
<ol id="ol_mshG" style="list-style-type: lower-alpha;" name="ol_mshG">
<li id="li_UhQM" aria-level="2" name="li_UhQM">Do not include a comma in <em>100000</em>.</li>
</ol>
<li id="li_HGVF" aria-level="1" name="li_HGVF">Select <strong>OK</strong>.</li>
<ol id="ol_bHjV" style="list-style-type: lower-alpha;" name="ol_bHjV">
<li id="li_Vvbi" aria-level="2" name="li_Vvbi">Excel will create a list from 2 to 100,000 that grows exponentially by multiplication. </li>
</ol>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_2.11.jpg" alt="BUS115_image_2.11.jpg" id="img_hWpW" name="img_hWpW">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 2.11</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<p id="p_oJsP" name="p_oJsP">The list will multiply by two each cell down. You may notice in <strong>Figure 2.11</strong> the data does not end with 100,000. Rather, it ends on the number immediately before 100,000. This is because if 65,536 were to be multiplied by two, it would result in a number greater than 100,000. If the data cannot get to the specific stop value, it will stop at the highest number it can get to below the stop value. The same selecting-and-dragging feature works on formulas as well. This will be covered in future chapters.</p>
<p id="p_kNxi" name="p_kNxi"></p>
<div class="card answerKey"><a class="card-header" data-bs-toggle="collapse" data-bs-target="#answerKey_eaah5" aria-expanded="false" aria-controls="answerKey_eaah5" href="https://books.byui.edu/bus_115_business_app/" data-shortened-url="https://books.byui.edu/-SySv" data-unshortened-url="https://books.byui.edu/bus_115_business_app/">When completed, your practice worksheet should look like the following:</a>
<div id="answerKey_eaah5" class="collapse card-body answerKeyAnswer">
<figure class="image text-center"><img class="img-fluid col-12" src="https://books.byui.edu/content_images/bus_115_business_app/JXqp_1.png" alt="BUS115_image_2.12.png" id="img_DMMW" name="img_DMMW">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 2.12</em></span></figcaption>
</figure>
<p id="p_YVqL" name="p_YVqL"></p>
</div>
</div>
<div class="clearfix">
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_xrEx" name="h3_xrEx">Supplemental Resource</h3>
<p id="p_rIZq" name="p_rIZq"><iframe id="kaltura_player_1636661561" src="https://cdnapisec.kaltura.com/p/1157612/sp/115761200/embedIframeJs/uiconf_id/33020032/partner_id/1157612?iframeembed=true&playerId=kaltura_player_1636661561&entry_id=1_rqmufcrw" width="480" height="270" frameborder="0" allowfullscreen="allowfullscreen"></iframe></p>
</div>
</div>