Advertisement

Blog

Excel: Taken for Granted, Part 2

Welcome back to my blog on the features of Excel that are often overlooked in our hurry to be productive. A while back, we had part 1. Moving around an Excel worksheet can be challenging, especially when it is a large worksheet. The following table details the more common keyboard shortcuts for getting around.

In the top left corner of the worksheet is the goto bar, as shown in Figure 1. When you enter a cell location here, you are taken immediately to that cell.

Figure 1

The goto bar is circled.

The goto bar is circled.

In the real world, we often use names to reference an object or group of objects, and it is convenient to follow suit in Excel. The goto bar has a double purpose; in addition to taking you to a cell, it will let you name a cell or group of cells. First, select a cell or group of cells (and they need not be contiguous). Then click in the goto bar and enter the name you want to use.

You cannot use a name that also refers to a cell (such as R10 or even RE10). However, Res10 would work for resistor No. 10. As mentioned before, when used in a formula, these names become absolute cell references. The dropdown arrow on the right of the goto box will list the named cells. Clicking on one will take you there.

You can also name other objects. A chart is one example, but you can go a step further. You can name both constants and formulae. Let's say you want to use “pF” as a constant throughout a worksheet to define picoFarads. Click the Formulas tab on the ribbon, and then click on Define Name . A dialogue like the one in Figure 2 will pop up. Enter the text “pF” as the Name and the formula in the Refers to field. Now, if you want to enter the value of 220pF in a cell, enter the formula =220*pF, and the value will be scaled accordingly.

Figure 2

Completed dialogue to define the constant 'pF.' The Define Name activator is circled in brown. To edit names, click on the button circled in red.

Completed dialogue to define the constant “pF.” The Define Name activator is circled in brown. To edit names, click on the button circled in red.

Let's look at naming a formula. Say we want to calculate power dissipation based on current and resistance (I2 R). Start by selecting a cell for current H4 and resistance I4. In this case, we will always calculate the power dissipation and place it one cell to the right of the resistance (J4). As you did with the Define Name sequence above, enter “PowerDissipation” as the Name, and enter the formula =H4^2*I4 in the Refers to field. Now, whenever you enter the formula “=PowerDissipation” into a cell, it will calculate the vale based on the product of the square the value of the cell two columns to its left and multiplied by the cell one column to its left.

Looking at a worksheet, it is impossible to know what cells and areas are named. Access and editing is done by clicking on the Name Manager button shown in Figure 2.

In some worksheets, it is easier to use intermediate calculations. It is possible to hide columns and rows to prevent confusion and partially protect the data. Select the columns (or rows), right click, and select Hide . To reverse the process, do what is intuitive — select the columns (or rows) straddling the hidden columns, right click, and select Unhide .

Figure 3

Panes can be handled by menu items (circled in brown) or by horizontal or vertical drag bars (circled in red).

Panes can be handled by menu items (circled in brown) or by horizontal or vertical drag bars (circled in red).

Sometimes it is convenient to keep one area of the worksheet (like titles of columns) constant on the screen while working elsewhere. You can achieve this using panes, which can be activated through the View tab, as shown in Figure 3. However, it is easier to create the pane by dragging the associated bar (as seen in Figure 3) down or across. Switch from one pane to the other by clicking in the desired pane area. To get rid of the pane, simply drag the bar back to where it was.

Excel will allow your text to overflow a cell into the next cell, provided that next cell is empty. If it isn't, the text will be truncated, or values will be replaced by the string ####. You can expand the column width or height by moving the mouse pointer to the list of rows or columns. Hover over the row or column boundary (where the mouse icon changes to a double-headed arrow), and then drag to the width or height you want. You can automate this to the widest or highest requirement by double clicking, rather than dragging the two-headed icon.

In part 3, I will deal with adding comments, adding and naming worksheets, and cross-worksheet access. I will also touch on protecting and auditing a worksheet.

32 comments on “Excel: Taken for Granted, Part 2

  1. geek
    May 22, 2014

    I think definining names using variables is one of the most useful functions in Excel. Particularly when you're dealing with a value that's going to be used a large number of places in a workbook and that value may change often, it's best to define it as a named variable rather than put the number in a cell.

  2. samicksha
    May 26, 2014

    To me the most frustating moment while working in excel is when formula dont give desired output, i guess its all about working with understanding on absolute and relative referencing

  3. Netcrawl
    May 26, 2014

    I think in every new version of Excel we get a number of new functions designed to help us work more efficiently. for example in Excel 2013, we get 10 new functions that could help us in our works. 

  4. amrutah
    May 26, 2014

    @tzubair: I totally agree with you, that naming the cells helps a lot. I use it a lot.  For any of my calculations I can directly type in the equations, such as Vtap=R2/(R1+R2)*Vhead, where all the variables are some cells.   It helps for someone who is using the workbook and improves readbility.

     

  5. amrutah
    May 26, 2014

    Aubrey,  Thanks for this series of excel.  These are really taken for granted, but sometimes I find it difficult when moving from MS-office to its linux counter-parts.   What you have to say about the free versions of the office apps, which comes closer to MS-office?

      On a lighter note, in the figure 2 above, the name is “pF”, but the value it referes to is “1e-9” 😛

  6. antedeluvian
    May 26, 2014

    amrutah

    What you have to say about the free versions of the office apps, which comes closer to MS-office?

    I have no experience with free versions of office apps. I don't have a strong aversion to Microsoft products, only some minor irritations. Since I always seem to have Office on whatever machine I am using I have never felt the need to investigate other options.

     

    On a lighter note, in the figure 2 above, the name is “pF”, but the value it referes to is “1e-9”

    Nice catch. Thanks.

  7. samicksha
    May 27, 2014

    I agree you, but with every new update or release our mode of work is getting more precise and specific which makes us to explore new/ more formula and new ways.

  8. geek
    May 27, 2014

    “To me the most frustating moment while working in excel is when formula dont give desired output, i guess its all about working with understanding on absolute and relative referencing”

    @samicksha: I agree that relative and absolute referencing can sometimes be a nightmares particularly for novice users. What essentially the user needs to do is perhaps draw a map of cells on a piece of paper and try to visualize the impact of the formula before typing it in and fixing it for a long time.

  9. geek
    May 27, 2014

    @amrutah: I think it becomes more handy when used with variables who's value change constantly and they're used at a number of places in the workbook. It's easy to update the value at just one location centrally and the impact goes into the entire workbook.

  10. samicksha
    May 28, 2014

    Sounds good way out, but we engineers are tend to find the simplest solution for every job..may be this attitude resisits to explore maximum efficiency of tool.

  11. RedDerek
    May 28, 2014

    @samicksha – …when formula dont give desired output…

    I run across this once in a while as well. Trick I use is to select the cell, then press F2 to edit. What you will find is that the formula my not be referencing the proper cell. This is now seen by the color of the cell reference and, as one uses the navigation bars to move around (do not move from the cell) you can see the values the formula is using. A hand calculation would validate what is actually being used.

     

    This latest round of tips are very useful and I have been using them all for years. It was only self-discovery I found them over time. 🙂

  12. samicksha
    May 29, 2014

    Thank's @RedDereck, sounds like solution. Let me check if this can me to get more than just checking pattern.

  13. chirshadblog
    May 30, 2014

    Samicksha: Indeed we all try shortcuts and due to that we do miss certain areas. IMO I feel exploring an issue is the best solution since then it addresses the root cause too. 

  14. chirshadblog
    May 30, 2014

    @samicksha: Tools are being developed to increase efficiency. So best advice is to use the tools as much as possible but in a much more productive manner. 

  15. eafpres
    May 31, 2014

    Hi Aubrey–great stuff.  You mention cases where you want the column headings to remain visible.  I almost always use Freeze Panes in the View part of the ribbon for this purpose.

  16. eafpres
    May 31, 2014

    A movement shortcut I use a lot is to End (the key labeled End on the keyboard) followed by a movement arrow.  When you have selected a cell in a sequence of cells, this takes you to the cell adjacent to empty cells.  For instance if you have a table of numbers, and you have selected a cell within it, using End then right arrow takes you to the righmost column of the table.

  17. eafpres
    May 31, 2014

    Another shortcut I find handy is for a case where, let's say in column B you have 100 cells with values (either values in the cells, or the results of formulas in the cells), from cell B2 to B101.  Now suppose you want to calculate in column C a formula using the values in column B, like = B2^2*PI().  if you enter the formula in the first cell in column C (C2 in this case), then select a cell in column B and use my shortcut of End followed by down arrow.  That puts you immediately to the last cell in the list.  The right arrow to column C (going to C101 in this case), and hold down the shift key, and press End followed by up arrow.  You have now highlited all cells from C2 to C101.  You can then copy the formula all the way down by using Alt then “e” then “i” then “d”.  This last key sequence is “fill down”.

  18. geek
    May 31, 2014

    “but we engineers are tend to find the simplest solution for every job..may be this attitude resisits to explore maximum efficiency of tool.”

    @samicksha: Yeah I've seen that attitude and frankly in the long-run this leads to more inefficiency and wastage of time. A lot of effort has to be put in to understand what was done earlier and that's something that should be avoided.

  19. samicksha
    June 4, 2014

    I have to admit, the fact cannot be denied. But again its how you evolve as an engineer.

  20. amrutah
    June 18, 2014

    @tzubair: I think you got only one part of it.

       What I wanted to tell is, if you name a cell as “VHEAD” instead of A1, “R1” instead of A2, “R2” instead of A3, then in order to determine the value of VTAP (anywhere in the workbook), I can directly type in as R2/(R1+R2)*VHEAD instead of $A$2/($A$2+$A$3)*$A$1.

      First thing this does is, it iimproves the readability and secondly acts as variable. If I change cell A1, I am infact changing VHEAD voltage everywhere.

  21. amrutah
    June 19, 2014

    eafpres1: This is fast and very nice.  I used to select C2 and then drag all the way to the last cell or used to copy the formula manually.
      Thanks.

  22. geek
    June 27, 2014

    @amrutah: Yes, giving the cell a name that's easy to recall makes it very easy to reuse it throughout the worksheet. The user does not have to remember the name of the cell. That's the primary advantage. Another secondary advantage is the reusablity where every time you change the value of that variable, the change gets applied at places in the worksheet where that variable is used.

  23. yalanand
    June 30, 2014

    @eafpres1, thanks for sharing the shortcuts. Really fast and useful short cuts which makes working more efficient.

  24. yalanand
    June 30, 2014

    First thing this does is, it iimproves the readability and secondly acts as variable. If I change cell A1, I am infact changing VHEAD voltage everywhere.

    @amrutah, can we apply the same logic to lists or is it just limited to cell variable ?

  25. yalanand
    June 30, 2014

    Yeah I've seen that attitude and frankly in the long-run this leads to more inefficiency and wastage of time. 

    @tzubair, I think every week we should spend time on learning automation and we should try to explore how best we can do the implementation. This helps us to explore more efficient ways of implementation and thus improves our productivity.

  26. amrutah
    June 30, 2014

    @Yalanand: I think defining names can be done for the cells, I don't know if we can define a group of cells.  I need to see if we can use the same “defining range” to define a group of cells or list.

  27. yalanand
    June 30, 2014

    So best advice is to use the tools as much as possible but in a much more productive manner. 

    @chishadblog, Companies should also encourage their employees to improve their productvity. Companies can conduct regular trainings and workshops to the employes to improve thier productvity.

  28. amrutah
    June 30, 2014

    @Yalanand: There are so many things that you can automate and various scripting languages.  But I think one should take Excel handling seriously and learn using it.  There are so many things that can be done, macros, charting, database management, calculations and so on.  Every intern or college students should be taught what I call “Engineering Excel” along with the CAD tools.  What Aubrey has put is something similar to “Engineering Excel”.  For Linux guys, Perl and shell programming is another automation everyone should learn.

  29. yalanand
    June 30, 2014

    I need to see if we can use the same “defining range” to define a group of cells or list.

    @amrutah, I did some quick search and found that same thing can be applied to lists as well. This definitely helps us to deal with data easily because remembering variables is lot easier than remembering cell address.

  30. amrutah
    June 30, 2014

    @Yalanand:  That's great.  I had read about it but have not used it.  Thanks for the info.

  31. yalanand
    June 30, 2014

    Every intern or college students should be taught what I call “Engineering Excel” along with the CAD tools.

    @amrutah, that is an excellent idea. Engineering colleges mostly concentrate on spice, verilog, magic etc. But its very important to train them on excel and VBA as well. But many colleges dont install Windows and Office because it costs heavily hence they usually install free tolls.

  32. amrutah
    June 30, 2014

    @Yalanand: “But many colleges dont install Windows and Office because it costs heavily”

       I agree that the Windows and Office are costly, but the student version is available and free though limited in usage.  We can teach using the free version of excel itself.  The shortcuts might be different, but still the usage will remain pretty muc the same.

Leave a Reply

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