Advertisement

Blog

Excel: Taken for Granted, Part 3

Editor’s note: This blog follows on Excel: Taken for Granted, Part 1 and Excel: Taken for Granted, Part 2.

Documentation of all projects is important, and Excel provides a method for that by allowing comments to be associated with a cell. When the mouse pointer hovers over the cell, the comment will pop up. You can use this for operator instruction or a self-reminder. Creation of a comment is trivial. Right-click on a cell (or group of cells, although only the first is selected) and select Insert Comment . A yellow box will pop up. Normally it will include your name as the first line of the comment, but it can be deleted easily. You can add text, paste text, format text, and size the box to suit the message.

Clicking away from the box will establish the comment and indicate there is one by means of a small red triangle in the upper right corner of the cell. Once there is a comment, right clicking on the cell will allow you to delete and edit the comment. You can also select the Show/Hide Comments which, when active, will keep the comment for that particular cell continuously visible.

An Excel “workbook” consists of one or more worksheets denoted by the tabs that are visible along the bottom of the window. You can add a new worksheet by clicking on the new worksheet tab (on the extreme right of the tabs). You can also right click on a tab and select Insert… | New Worksheet . Right clicking the tab also provides several other options, which include renaming, and you can use symbols as well as text — I once used π when working with MTBF data. Re-ordering the tabs is merely a case of dragging the tab to where you want it in the tab sequence.

Figure 1

Right-clicking on a tab will allow several options. The circled tab is the new worksheet tab.

Right-clicking on a tab will allow several options. The circled tab is the new worksheet tab.

You can access data from other worksheets by prefacing the cell with the sheet name and an exclamation point (exclamation mark if you speak the Queen’s English). For example if we want the value of cell B2 from Sheet2 to appear in cell C6 on Sheet1, the contents of cell C6 will be =Sheet2!B2 .

Working on complex worksheets can lead to problems. Excel provides some tools to help diagnose these problems, and these can be found under the Formulas ribbon tab as seen in Figure 2. These show which cells contribute to a value (precedents) and how a cell affects others (dependents). Just because Microsoft says these are for auditing does not mean that that cannot be used for your own nefarious purposes. Take a look at my design idea, “Use Excel To Develop A Traceability Matrix,” to get an idea of what I mean.

Figure 2

The auditing tools are circled in red. Calculation control is circled in blue.

The auditing tools are circled in red. Calculation control is circled in blue.

By default, Excel recalculates at every change on the worksheet. On complex calculations this can take some time, and so you can prevent the continuous update and recalculate on command using the Calculation controls you can see in Figure 2.

Even though it is probably common knowledge to all of you, I thought I should mention that it is possible to protect worksheets and areas on the worksheet so that critical data and formulas cannot be changed inadvertently. When a sheet is protected, by default all cells are also protected. You have to unprotect the cells that are allowed to be changed by right clicking the cell (or group of cells) and clicking Format Cells … and select the Protection tab. To activate the protection on a sheet you can right-click a sheet tab (see Figure 1) or go to the Changes section of the Review tab on the ribbon. There are quite a few types of changes that you can protect against, as you can see in Figure 3.

Figure 3

Choices in protecting a worksheet.

Choices in protecting a worksheet.

If you create a file that you want to use as a prototype, you can save it as a template by doing just that — File | Save as , and select the Excel template from the drop down menu. When you click on File | New there is an option, My templates , which will open the template as a new file.

So there you have it. I do hope that I managed to impart some new information to you and that presuming this knowledge up till now did not detract from earlier blogs. My next blog on Excel (which may not actually be my next blog) will create an application that builds on a number of the topics that I have covered. It will select a pair of resistors that will provide the minimum of error for a given formula.

39 comments on “Excel: Taken for Granted, Part 3

  1. RedDerek
    May 28, 2014

    Protecting cells is a very valuable tool when sharing one's spreadsheet with others so that someone cannot mess up the formulas. The protection can be password protected so that one cannot just turn if off. Also, you could hide the equation to keep the trade secret.

  2. Yvon.Hache
    May 28, 2014

    Thanks for all this EXCEL info.  I'd like to add another way to comment a formula by adding +N(“this is my comment”) at the end of the formula.  This could be useful to explain the value or the formula and will will follow it if copied.

    Example:

    = A1 * B3  +N(“I like to use A1 in my formulas”)

     

  3. antedeluvian
    May 28, 2014

    Yvon

     I'd like to add another way to comment a formula by adding +N(“this is my comment”) at the end of the formula

    Thanks for this. It is fascinating. I had never heard of this. I searched on Google for an expalnation this is what I found here:

    The unconventional way: Use the N function
    Excel's “number” function, N, provides an unconventional way to annotate the formulas in your worksheet. The N function takes the form =N(item), where item can be a string, a value, or any valid expression. You typically use the N function to convert a string to a number. (There are other uses for this function, but we won't go into them here.)

    The key to this trick is that N returns a value of zero when item is a string. So, if you want to explain why you hard-coded a certain value in a function call or if you simply want to write yourself a note to remember why you used a certain value or function, use N in the following form:
    +N(“Write your comment here”)

    Because you provided the N function with a text string, it returns zero.

  4. antedeluvian
    May 28, 2014

    My next blog on Excel (which may not actually be my next blog) will create an application that builds on a number of the topics that I have covered. It will select a pair of resistors that will provide the minimum of error for a given formula.


    It will be my next blog. It has been submitted to Steve.

  5. Yvon.Hache
    May 29, 2014

    A friend told me about the +N() several years ago but i never looked for more detail about it.   I guess I was taking it for granted.  🙂   Thanks for digging for this.

  6. Netcrawl
    May 29, 2014

    Sorry for that I missed a great chat, @Yvon.H its a very useful stuff. Actually you can use command to tell Excel what to do, for Excel 2007 we use Ribbon to issue a commands, the Ribbon is located near the top of the Excel Window just below the Qucik Access toolbar.

  7. chirshadblog
    May 30, 2014

    @Yvon: Yes most of us ignore certain things even though it shows some promise. It only gets noticed when the requirement is there for us. Sometimes that might be the downfall

  8. chirshadblog
    May 30, 2014

    Example:

    = A1 * B3  +N(“I like to use A1 in my formulas”)

    This gives me an error. Help of this would be greatly appriciated

  9. antedeluvian
    May 30, 2014

    chirshadblog

     

    = A1 * B3  +N(“I like to use A1 in my formulas”)

    This gives me an error.

    If the line is exactly as shown, the inverted commas ” at the end is superfluous (in bold and underlined above). They should be removed.

  10. Yvon.Hache
    May 30, 2014

    Keep in mind that this only works when the result of the formula is a value. 

  11. geek
    May 30, 2014

    @Yvon: I have extensively used comment in the cell feature and I think it is one of the most useful functions in Excel. Firstly when you're going to refer back to the sheet, it helps you understand the logic behind the formula you wrote. Secondly, if you share the sheet with someone else, they can easily understand your formula and why you wrote it.

  12. geek
    May 30, 2014

    “The protection can be password protected so that one cannot just turn if off. Also, you could hide the equation to keep the trade secret.”

    @RedDerek: I think this transforms the usability of the worksheet greatly. From a mere worksheet, it can become a mini application because it only gives the user access to certain controls and you cannot mess around with the sheet much. I find it very useful when I want other people to only fill in values and get the results.

  13. geek
    May 30, 2014

    @antedeluvian: It'd be great if you can also explain the use of common UI controls like text boxes, drop-down menus, command buttons etc. The use of these can greatly enhances the usablity of excel worksheets and make them look like actual applications.

  14. antedeluvian
    May 30, 2014

    tzubair

    It'd be great if you can also explain the use of common UI controls like text boxes, drop-down menus, command buttons etc.


    I will keep it in mind. Some of the controls can be used without resorting to VBA and I will look into it. I have tried to keep away from VBA because of the blog format and because is quite powerful and you can do quite a lot without having to program as well.

    I was sure that I had covered the Data Validation feature (on the ribbon data tab and then select Data Validation button)  in one of my blogs, and it certainly comes up in my next one, although it presupposes that I discussed it elsewhere. Data Validation is often a very simple alternative to controls and does easily allow for drop downs and checking that the data is in fact of the right format and within limits. I will do a more detailed check- if I haven't covered it, there will be a blog.

     

  15. Yvon.Hache
    May 30, 2014

    Have you discussed “conditional formatting” before?  If not, this can be an interesting subject as it can serve as a powerful tool.

    Speaking of control, one that I like is attaching a scroll bar to a cell and by sliding up/down, it varies the cell value.  Combined this with a chart, it could be a nice tool for the whatif scenarios  (developer tab – Insert – Form Control/Scroll bar).

  16. antedeluvian
    May 30, 2014

    Yvon

    Have you discussed “conditional formatting” before? .

    Excel: Conditional Formatting

     

     

  17. antedeluvian
    May 30, 2014

    Data Validation is often a very simple alternative to controls and does easily allow for drop downs and checking that the data is in fact of the right format and within limits. I will do a more detailed check- if I haven't covered it, there will be a blog.

    I began writing this series of blogs on Excel for Microcontroller Central. When it was shut down, Brad Albing (then editor of Planet Analog) agreed to copy the existing data base across as a base for the new blogs that I had up my sleeve. When I gave him the list, I obviously forgot the one on Data Validation, which is essential to complete the series. I have asked Steve to add the blog to the archives.

     

    I knew I had written about it!

     

    Watch here for updates.

     

  18. Netcrawl
    May 31, 2014

    @Antedeluvian I believe we can use a list from another workbook as the source for data validation dropdown list and for data validation to work, the workbook which contains the source list must be open.  

  19. geek
    May 31, 2014

    “I have tried to keep away from VBA because of the blog format and because is quite powerful and you can do quite a lot without having to program as well”

    @antedeluvian: I agree. Although VBA has its own merits, it can make things complicated for non-technical users. I have used a few UI controls and have been able to program through the use of on-screen macros. That did not involve going into VBA at all.

  20. Netcrawl
    June 1, 2014

    @Tzubair You can do programming in Excel using VBA, VBA is an extremely powerful tool that users can use to manipulate, analyze and present data. Excel has Visual Basic for Applications, a programming languages that give users the ability to extend those applications and to perform repetitive task that UI does not seem to address. VBA enable you to build new capabilties in Excel, for example a new algorithm to build to analyze a data or formula and use Excel's charting features to display the results.   

  21. Davidled
    June 1, 2014

    Excel has one of advantage for implementing database sorting and classifying according to criteria with a powerful visual interface.  I remembered that one of support engineers made a troubleshooting program with database and distributed it.

  22. antedeluvian
    June 2, 2014

    DaeJ

    Excel has one of advantage for implementing database sorting and classifying according to criteria with a powerful visual interface

    You are quite right. I really don't use it much though, so I still need to go through a learning curve before I would feel confident enough to write about it- and I would need to come up with a suitable application in the electronics world.

     

  23. Davidled
    June 2, 2014

    There is Microsoft Excel Workshop schedule for 2014: This training covered a lot of topic.

    http://www.fast3.illinois.edu/workshops/excel.htm

  24. antedeluvian
    June 2, 2014
  25. geek
    June 8, 2014

    @Netcrawl: I agree. I have been using VBA to make my spreadsheets more powerful and have found them to be extremely useful especially when the output is required in the form of a spreadsheet. I'd prefer the use of VBA and spreadsheets over other applications involving Visual Studio and SQL unless there's a real need.

  26. GSKrasle
    June 25, 2014

    @tzubair:

    Comments are also a handy place to stash a “backup copy” of a complicated formula JIC it gets accidentally deleted, or needs to be temporarily replaced.

  27. GSKrasle
    June 25, 2014

    Excel is my favourite video-game, and I could say a lot about it, a lot of tips and tricks, but I wonder if this is an appropriate forum; there are many dedicated ones already.

    Buuut, I would like to point-out that, as nice as all the old versions always have been, there is at least one reason to update to the 2013 version: the FORMULATEXT() function!

    It does what it sounds like, retrieves the text you would see in the formula bar when you select a cell. It's useful for making formulae visible to aid understanding. I like to use it this way:

    =IFERROR(FORMULATEXT(G5),G5)

    But I also like to use it in a way that it was never intended for: extracting the filename of a reference to a different spreadsheet: that allows me to quickly and easily construct and manipulate data from several sources.

    Here is how I use it, showing the contents of the various cells in col G:

    G5: =[Test_Spreadsheet.xlsx]Sheet2!$C$3

    G6: A

    G7: 1

    G8: =IF(ISBLANK(G5),””,RIGHT(FORMULATEXT(G5),LEN(FORMULATEXT(G5))-1))
    (this gets the text “[Test_Spreadsheet.xlsx]Sheet2!$C$3”)

    G9: =IFERROR(MID(G8,FIND(“[“,G8)+1,FIND(“]”,G8)-FIND(“[“,G8)-1),IF(ISERR(FIND(“”,G8)),””,MID(G8,FIND(“*”,SUBSTITUTE(G8,””,”*”,LEN(G8)-LEN(SUBSTITUTE(G8,””,””))))+1,LEN(G8))))
    (this gets just the file-name, which I like to use in chart titles, legends, and such for presentations.)

    G10: =IF(OR(G$8=0,ISBLANK(G$8)),””,INDIRECT((LEFT(G$8,FIND(“$”,G$8)-1)&”$”&G$6&”$”&ROW()-ROW(G$10)+G$7)))
    (this retrieves the contents of cell A1 of the same spreadsheet referenced in cell G5.)

    So, to retrieve data from a different (open) file, all I  have to do is change the one reference in G5, and the data and the graph legends, etc., all automatically update. The only problem is that the other file has to be open to get the data. So, once I have everything arranged, I copy and “paste values” the data. But then, nobody can see my beautiful formulae! I copy the formulae's texts into the comments so that the “paste values” doesn't obliterate them and I can reconstitute the functionality when I need to make a new analysis (finally, we get to the reason I wrote this comment!)

     

  28. geek
    June 27, 2014

    “Comments are also a handy place to stash a “backup copy” of a complicated formula JIC it gets accidentally deleted, or needs to be temporarily replaced.”

    @GSKrasle: Yes, that's certainly one important use of them. Never thought of it. Thanks for the suggestion 🙂

  29. Sachin
    June 30, 2014

    There is Microsoft Excel Workshop schedule for 2014: This training covered a lot of topic.

    @DaeJ, thanks for sharing this info. I am curious to know if we can enroll to the classes and attend the classes on-line ?

  30. Sachin
    June 30, 2014

    Excel has one of advantage for implementing database sorting and classifying according to criteria with a powerful visual interface.

    @Daej, I agree with you. But one disadvantage of excel is if the database is huge then it processes the data very slow. I think query in MS-Access is pretty fast compared to excel.

  31. Sachin
    June 30, 2014

    VBA is an extremely powerful tool that users can use to manipulate, analyze and present data.

    @Netcrawl, I totally agree with you. People have even built Piano using VBA. We can do many complex modelling using features of Excel and VBA.

  32. Netcrawl
    June 30, 2014

    @SachinEE, Yes its pretty fast in MS Acess but its takes more time and training to use it well, the learning curve of building databases in Access may seem qute daunting. And much more complicated than putting putting data on a cells in Excel.  In Excel its quite different we have a short learning curve, you could be productive right a way.  

  33. Netcrawl
    June 30, 2014

    @SachinEE, piano? using VBA, that's new, how? I didn't know that. I'll been using Visual Basic for years, I'll used MySQL and SQL Server not Access for database. Access is too slow when it comes to database programming.  

    Microsoft has done a very innovative works here, embedding VBA in Excel, I think the primary purpose of using VBA in Excel is to extend fucntionality and capability, do much complex things. 

  34. amrutah
    June 30, 2014

    @Netcrawl:  I agree with you.  The database handling is fast but the time for learning it takes is huge.  If you know SQL then we can use start using MS Access quickly and build up database.  But the other advantage that I see is, MS excel is good at calculating, hence once the database collection is done, calculating, charting and displaying the data is fast and superb.

  35. yalanand
    June 30, 2014

    piano? using VBA, that's new, how?

    @Netcrawl, I thinki remembering seeing excel sheet which had Piano code in it. It looked something similar to this : https://www.youtube.com/watch?v=OJ_eWO0Kpp0

  36. yalanand
    June 30, 2014

     If you know SQL then we can use start using MS Access quickly and build up database.

    @amrutah, building SQL data base looks pretty tedious but it isn't. Writing simple queries in SQL is very simple and believe me its very fast compared to Excel.

  37. SunitaT
    June 30, 2014

    @GSKrasle, suggestion you gave makes the file handling so easy. Thanks for the suggestion.

  38. amrutah
    July 1, 2014

    @Yalanand: “queries in SQL is very simple”

       I agree, that SQL is simple, but the frontend being Excel makes a lucrative deal.

  39. willkarter
    March 21, 2016

    If the support from microsoft office would would have been better, it would have been such a great product to use. But unfortunately the live support is not active.

Leave a Reply

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