Advertisement

Blog

Excel: Taken for Granted, Part 1

When I started my blogs on Excel, I launched straight in without any mention of some of the simple features that you may or may not know. As I continue this series, I thought perhaps I should cover some of these features. I have covered some of them implicitly to date, but it may help to be explicit before proceeding further.

Often you want to create a sequential list, for instance the numbers from 1 to 100 to identify rows in a table. In Excel you could enter the number 1 in cell B2, and then enter the formula =B2+1 in cell B3, and then copy cell B3 down to cells B4, and so on. Cell B4 will contain the formula =B3+1 , B5 will contain the formula =B4+1 , and so on. Note the transformation of the row number each time pointing at the cell directly above it — we will get back to this in a moment.

This technique certainly has its place, but Excel has an “Autofill” feature, which will speed things up if you don’t need the automatic update that would happen if you insert a row, for instance. Let’s see how this works. First enter the number 1 in cell B2 and 2 in B3. Block B2 and B3, and you will notice a small black rectangle at the lower right corner of the block outline. Click and drag the rectangle downward, and you will see a ghost number appear indicating what the autofill would enter in the cell that you have reached. This is shown in Figure 1.

Figure 1

Autofill in action. The Autofill drag rectangle is circled.

Autofill in action. The Autofill drag rectangle is circled.

On releasing the mouse button all the cells to that point will be filled in. But Autofill is pretty smart. It will do preprogrammed things like dates, but will also made a valiant attempt at many other sequences you throw at it. When the autofill is complete you will see a symbol appear at the lower right of the block (Figure 2). If you click on it you can influence the kind of autofill you want. Updating the selection will immediately update the recently filled cells.

Figure 2

Options for the autofill pop up when you click on the circled symbol.

Options for the autofill pop up when you click on the circled symbol.

You can also pre-define your own lists, like all the resistor values. However there are two caveats. First, the list has to be in text format. Second, the resulting autofill list is installed into Excel itself and not the worksheet, so distributing the list to others needs a bit of forethought. To create a list, enter the data in Excel in a single column or row. You enter the number as text by preceding it with the apostrophe ('). (Formatting the column as text will not work for this purpose.)

Excel will place a small green triangle in the left corner of each cell as a warning that it is a numerical data formatted as text. To enter the list, click on the ribbon tab File followed by Options | Advanced and then scroll down to the General heading and click on the Edit Custom Lists button. Block the list and click on the Import button. Transporting to another machine would require you to place the whole list in a worksheet and transport the worksheet and then import the list all over again.

As they say on TV, “But wait! There’s more!” This tiny rectangle is really something. If you enter a formula in one cell and then drag the rectangle, the formula will follow along. “And even more…” Enter the formula alongside the column you want to act upon and double click on the rectangle. Like magic, the column is filled exactly to the last populated row.

We saw earlier how Excel allowed for relative addressing changes when copying from one cell to another. This is an extremely powerful tool and is probably the primary reason that complex worksheets can be constructed with ease. Sometimes, however, you don’t want the transposition, and this is achieved by the use of the “$” symbol within the text reference, a technique that dates all the way back to Visicalc.

Preceding the column letter with a “$” means that the column will remain constant; preceding the row number with a “$” means that the row remains constant; and preceding both refers to a fixed cell. For instance, when referring to cell C55, $C55 means that any copy (and that includes block copies) will always refer to column C no matter where you paste the copy. Similarly C$55 means that any copy (and that includes block copies) will always refer to row 55 no matter where you paste the copy. $C$55 will always refer to cell C55 no matter where you paste the copy. Another way to set absolute addressing is to use named cells, which we will get to in the next part of this blog.

When you paste a copied cell or cells you will see a dropdown symbol at the bottom right of the pasted data, as you can see in Figure 3. Clicking on it will allow you to change the format of the copied data as you can see in the popup menu. For instance you may have calculated a bunch of results based on some formulae, and all you want to do is copy the results as numerical data. The Values (V) (3rd row 1st column) option will simply update the pasted data from formulae to data. Other actions are left for the reader to investigate “as an exercise.” If you choose to ignore this action the copy will be with the complete formatting and formula setup as the source cell(s).

Figure 3

Click on the circled icon to bring up the copy options.

Click on the circled icon to bring up the copy options.

There is still more stuff to cover. Excel is so intuitive that many of the more powerful aspects of seemingly trivial features are overlooked. I hope that this has proved interesting so far and that you have learned something. See you in Part 2.

22 comments on “Excel: Taken for Granted, Part 1

  1. antedeluvian
    May 8, 2014

    When you are trying variations on the paste option (as described in the text referring to figure 3) you should be aware that you can see the same options on the Home tab of the ribbon. (Keep in mind that Excel 2013 has moved away from the ribbon paradigm, if I am informed correctly.)

    However if you right click the target area there will be an option of Paste Special. This will shows the options in text without having to hover over icons to get a description.

    The Operation options perform the arithemetical action on the values in the target cell(s) combined with the source cells.

    Of some interest in my next series of blogs in Excel is the Transpose option which changes a column to a row and vice versa.

  2. amrutah
    May 8, 2014

    Another basic thing that I use a lot is the custom formatting.  For a scientific number format, I always choose ##0.0E+0

    Right click >> Format Cells >> Custom >> ##0.0E+0

      All that this does is, formats the cell to be in 10e6, 10e3,0,10e-3, 10e-6 and so notations much like representing Mega, Kilo, milli, micro.

  3. antedeluvian
    May 8, 2014

    amrutah

    Another basic thing that I use a lot is the custom formatting.  For a scientific number format, I always choose ##0.0E+0

    Thanks for that.

    All suggestions are appreciated.

  4. goafrit2
    May 8, 2014

    I have always asked where Finance will be without Excel. It is still a mystery that people spend so much to buy certain software when they have Excel in all their systems.The challenge has been that Microsoft in a bid to make it look like a simple tool did not emphasis on the need for users to be trained as most financial tools demand. Excel has made a contribution in improving productivity.

  5. SunitaT
    May 10, 2014

    It is very true that most people have take many features in excel for granted, this may be because they don't often use it or if they use it they use it for basic reasons. I am not that good in excel since most of my work does not relate to it but after reading this article I think I should try to practice on it further. Another thing that is limiting the use of excel is the advancement of the accounting software. Since excel is cheap, I think people should try to exploit it further.

  6. antedeluvian
    May 10, 2014

    SunitaTO

    Another thing that is limiting the use of excel is the advancement of the accounting software .

    I would have hoped that my series on Excel here on Planet Analog would have provided some inspiration of how to use Excel in electronics. Here is a list of all my blogs here on PA. The ones on Excel have “Excel” somewhere in the title.

    If you would like some more examples, just reply here and I will list a whole bunch more of Excel applications for Electrical Engineering.

     

     

  7. eafpres
    May 14, 2014

    @goafrit2–An important reason to use a commercial software package vs. spreadsheets is that most spreadsheets have errors and the culture of creating them does not include the same level of testing that most commercial software does.  Here is an article discussing the widely reported result that 88% of Excel spreadsheets have errors.

  8. eafpres
    May 14, 2014

    Hi Aubrey–more fun stuff, thanks.

    I use keyboard shortcuts for these things so much I forget where they are in the menus.  Alt-e-i-s is paste special in Excel, and I use that keyboard combination probably more than any other.

  9. eafpres
    May 14, 2014

    Aubrey–an interesting variation here is, let's say I have a sequential table of formulas in a worksheet.  For visualization, suppose that each column caculates a function of interest based on another table.  However, let's say that each row in my table of functions needs to address every 5th row in the other table.  That is, the 1st row of my table has, say, Q13, R13, S13, T13, U13 as data that are used in the formulae.  Now, what I would like to do is copy down the formulae to build the table.  Unfortunately, I haven't found a “copy down using every nth row” or something like that.  Here is how I solve that.

    I know that the next row I need to reference is 18 (13+5).  If I copy the 1st row, then paste in row 18, as long as I DO NOT use absolute referencing (with the $), then that pasted group will refer to Q18, R18…U18.  Now, the fun thing is I can block those cells and DRAG them back to row 2 of my table.

    Although a bit unweildy, it is very expedient if there aren't too may rows to build.

  10. antedeluvian
    May 14, 2014

    Blaine

    I use keyboard shortcuts for these things so much I forget where they are in the menus.  Alt-e-i-s is paste special in Excel, and I use that keyboard combination probably more than any other

    As always, thanks for a very pertinent contribution.

     

  11. eafpres
    May 14, 2014

    @SunitaT0:  There are even functions in Excel that are not documented.  For instance, there is a fast fourier transform function that isn't really supported by Microsoft as far as I know.

  12. RedDerek
    May 16, 2014

    I thought Aubrey covered this a few articles ago. But never hurts to bring it up again.

  13. RedDerek
    May 16, 2014

    Excel also is a good basic database functionality. I use this portion (VLOOKUP, etc) for managing my parts database when creating a board. I pull key parameters that trigger an inventory item number (if no existant I add it in), and I pull in the pricing structure. Then one just has to enter the quantity to build and the spreadsheet calculates total cost to build as well as cost per item built. Trick is to have the basic database of information that is in Access; here I just export tables to be used by the parts spreadsheet.

  14. bk1
    May 28, 2014

    Dragging the lower right corner of a rectangle is useful, but there is another use for that control.  If you have data in an adjacent column, you can double-click on the lower right corner of the current cell, and this column will autofill to the same length as the adjacent column.

    I often use this when importing data from a datalogger or other source that generates hundreds or thousands of rows, but needs to be processed and/or plotted.

  15. antedeluvian
    May 28, 2014

    bk11

    If you have data in an adjacent column, you can double-click on the lower right corner of the current cell, and this column will autofill to the same length as the adjacent column.

    Thanks for the tip- I can see how this can be very useful.

    So much from a simple black square!

     

  16. goafrit2
    June 2, 2014

    >> All that this does is, formats the cell to be in 10e6, 10e3,0,10e-3, 10e-6 and so notations much like representing Mega, Kilo, milli, micro.

    If Microsoft has packaed Excel with another flavor, it might have qualified for a really strong scientifc tool. They just call it another name and sell it to the scientific community. It is a great product and certainly the pillar of  Microsoft Office.

  17. goafrit2
    June 2, 2014

    Another thing that is limiting the use of excel is the advancement of the accounting software. Since excel is cheap, I think people should try to exploit it further.

    The key is the marketing. Microsoft has not made it to seem like accounting tool so that people do not think it is complicated with  a need of a special training or degree. The generic usage nature is part of the penetration strategy.

  18. goafrit2
    June 2, 2014

    .  Here is an article discussing the widely reported result that 88% of Excel spreadsheets have errors.

    Absolutely, the Harvard professor duo that came up with the conclusion that when debt tops 90% of GDP, economy shrinks had Excel moments. Yet, that is not an Excel problem. It is human error and the fact that no one wants to attend a class to learn the software unlike the commercial ones.

  19. goafrit2
    June 2, 2014

    >> Excel also is a good basic database functionality. I use this portion (VLOOKUP, etc) for managing my parts database when creating a board.

    Maybe you need to learn Microsoft Access which offers better value for that. It comes in the same bundle you have for Excel, so no extra expense needed. Access will do better for database in my own opinion

  20. goafrit2
    June 2, 2014

    >> Trick is to have the basic database of information that is in Access; here I just export tables to be used by the parts spreadsheet.

    You got the Access coverred. Excel is a nice tool but when it involves basic database, staying with Access seems to be better. I think it is faster to search and find things in Access where you have the freedom to customize the look and ways your data is stored. Excel gives you what it gives you but Access offers more.

  21. amrutah
    June 19, 2014

    Antedeluvian:  I have been following your blogs closely and are immensely helpful.  It would be really nice if you could collate few more examples of excel usability.

    Many Thanks.

  22. antedeluvian
    June 19, 2014

    amrutah

     

    I have been following your blogs closely and are immensely helpful.  

    Thanks

    It would be really nice if you could collate few more examples of excel usability.

    Well, there are two more in this blog, another on the Validate Data feand the a two parter the connects everything together. The last one is here. If you look on the right hand side you will see a list of the others I have just mentioned. Don't forget the free dowload!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.