It is summer time in Brampton and we are suffering through the highest temperatures in history. We have spent a lot of time hunkered down in our air conditioned home. Many days it was simply too hot to go out on our deck.
July has been a planning month for me. We are well along with plans for an outstanding Accounting Technology series this fall which will visit 14 cities from coast to coast. Registrations are closing in on 700 and some of the cities will sell out next month. To make sure you get a seat go to
www.k2e.ca to register.
September will see two new ventures. The first one is a new relationship
with the CPA community that will provide you with a catalog of webinars and recorded videos that are excellent and will qualify for full CPD credits.
Summary: Sometimes it is Useful to Activate the Formula Bar From the Keyboard. Here is how to do this:
The Formula bar, which is located just above the work area in Excel, shows the formula or value in the currently selected cell. You can click on the formula bar to edit information within a cell. What happens if you want to activate the Formula bar by using the keyboard?
The easiest way to do this is simply to press the F2 function key. By doing this you are telling Excel that you want to edit the contents of a cell. When you press F2, it is quite likely that Excel will simply expand the information in the cell and allow you to edit the information directly in the cell itself. If you don't like this behavior, but instead want F2 to activate the Formula bar, follow these steps:
Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 - 2016 display the File tab of the ribbon and then click Options.)
Click the Advanced option at the left of the dialog box.
Make sure the Allow Editing Directly In Cell check box is cleared.
Click on OK.
Now, whenever you press F2, the Formula bar is selected and you can edit the formula there.
Summary: In a large complex worksheet with hundreds of rows of calculated values, it is almost guaranteed that there will be a number of cells with #N/A error that you would like to be "0" (or some other value), so that the cells can be used in other formulas. Here is how to do this:
We will use the GoTo feature in Excel. Simply follow these steps:
Press F5. Excel displays the Go To dialog box
Click Special. Excel displays the Go To Special dialog box
Make sure the Formulas radio button is selected
The only check box that should be selected under Formulas is Errors
Click OK. Excel selects all cells where the formula returned an error value
Type 0 or whatever value you want.
Note that this approach results in any error values being replaced, not just those with the #N/A error.
Dates are a special case in Excel. If you enter information that can be translated as a date (by any stretch of the imagination), then Excel treats it as a date. It converts your data into a serial number that is internally used to represent dates and times. For instance, any of the following entries will be translated to a date by Excel:
December 10, 2016
If you enter the first example, Excel will convert it to a date and display that date as best it can determine. If you leave off the year in your entry, Excel assumes you mean the current year. You could also use dashes instead of slashes and Excel will still figure out you are entering a date.
Regardless of how you type a date, it is converted to a special serial number by Excel and stored internally in that format. How you see the date on your screen is a result of how the cell is formatted. Even though Excel stores dates in a standard format internally, they can be displayed using any number of different formats.
K2E Canada Inc. is a leading provider of professional development seminars for the Canadian accounting world. Each month we publish this free Office Tips e-mail newsletter. These tips will save you time and enhance the appearance of your Office files.
To subscribe to our K2E Canada newsletter send an e-mail to
firstname.lastname@example.org with "Subscribe to newsletter" in the Subject.
Your information is always kept secure and never shared.