advice you can use — short and to the point — every Tuesday, Wednesday & Thursday

technology  research  practice

All Our Technology Tips

When you create a cell reference in an Excel formula that refers to another cell, that cell reference can be relative (the default) or absolute. A relative cell reference adjusts to its new location when the formula is copied or moved. An absolute cell reference does not change when the formula is moved.

Consider this example: Starting in A1 you have a 3×3 table with some figures in it. Cell D1 contains this formula: =A1+(B1*C1). If copy this formula to cell D2, the cell references will change relative to the new location and the formula will automatically change to =A2+(B2*C2).

Now consider this example where you have a constant in a formula: You have a column of costs in Canadian dollars and you want to add a second column that gives the US dollar equivalent. And in particular you want to set it up so you can adjust the US conversion rate in one location on the sheet so you don’t have to do it multiple times if the exchange rate changes.

So in the first column you have your $Cdn figures (A1, A2, A3…). Your conversion rate is in cell D1 (e.g., 1.10). In cell B1 at the top of the second column you have this formula: =A1*$D$1. The $ in front of the row and column references indicates an absolute cell reference. So if you copy the formula in B1 to B2, it will become =A2*$D$1. The first part of the formula is relative and is changed, but the second part is absolute and did not change (if it had done so, it would have referred to the cell below the cell with your conversion rate.

Absolute and relative cell references can twist your brain, but they are real handy in the right circumstances. Note that you can also have a cell reference in a formula that is a mix relative and absolute. Will save an example of that one for another day.

So recognizing that a $ sign is used to indicated an absolute row or column reference, here are some examples of relative and absolute cell references:

  • A1 (relative column and relative row)
  • $A$1 (absolute column and absolute row)
  • A$1 (relative column and absolute row)
  • $A1 (absolute column and relative row)

And now for the tip. It should be obvious that typing $ signs in one or more cell references in a formula will be tedious and error prone. The brilliant programmers at Microsoft save you a bunch of grief with some keyboard shortcuts: Click on a cell references in a formula and press F4 on a PC, or Command+T on a Mac, to cycle through the 4 possible relative and absolute combinations for that cell reference. Very helpful indeed!

 

Most of you will say that typing 140 characters isn’t a big deal. But typing the same thing over and over again can be taxing, even if it is only 140 characters. And why type things unnecessarily if you can avoid doing so?
Hootsuite has a really neat feature that lets you save templates of tweets that you can drop into the Compose Tweet window with just a few clicks. This is how it works.HootsuiteLogo
Type the text you want make into a template in the Compose Tweet window. Look for the Save Message as Template button towards the bottom right of the Compose Tweet window – it is the little floppy disk icon to the left of the Send Now button. Click on it and the tweet you just typed will now be a template.
When you want to use a template tweet, click on the View Templates button – the small upside down triangle between the Save Message as Template and Send Now buttons. A list of your templates will appear and with a single click you instantly have a tweet in the Compose Message box.
The template feature is really helpful if you need to send the same tweet out multiple times or a series of similar tweets. If you have the Pro version of Hootsuite, you can share templates across teams which will help save time and make sure all your tweets stay on message.

 

All of us have experienced the frustration of a dying smartphone battery. While battery life has greatly improved over the years, a dead smartphone is something most of us see more often than we would like.baterycharging
Of course, turning of Bluetooth and WiFi when you aren’t using them will help extend your battery life, but dimming the brightness of screen can really help too, especially if you have a smartphone with a larger screen. Big screens look great, but they are a huge drain on your phone’s battery. Dimming the screen, even just a bit, will help you extend your battery life.
Within your smartphones settings you will finds a control that will allow you to dim the brightness of your screen. The dimmest setting will be difficult to read in most circumstances, especially if you are outdoors or in a brightly lit setting, but you will find something toward the middle will greatly extend your battery life.
And look for an auto-brightness option, using it can help you get the proper screen brightness in various lighting situations.

 

Occasionally you may find yourself wanting to type superscript (see the adjacent graphic) or subscript (102). You can do this through the Font dialog box, but there is a much faster way. superscript
For superscript, simply press Ctrl + Shift + + (press and hold Ctrl and Shift, then press +). For subscript, press CTRL + = (press and hold Ctrl, then press =). Pressing the respective shortcut again will get you back to normal text.

 

Every so often you will accidently close a browser window tab that you wanted to keep open. Hate when that happens!
And after doing so, you want to get it back. Typically, most of us will open Google Search in a new tab and try to reconstruct the search terms that got us to the tab in the first place. Sometimes you might open a new tab and go into your browsing history. And while doing either these things will probably get you back, there is a must easier and faster way to get that tab back.
Pressing Ctrl+Shift+T instantly and magically opens the most recently closed tab on exactly the same page on most Windows browsers. Cmd+Shift+T does the same thing in Chrome on a Mac and on Safari use Cmd+Z.

 

Most of you will be used to going into the Control Panel to tweak or change your Windows settings. In the Control Panel, related types of settings are grouped under different icons to help you find the setting you are looking for. But finding a particular setting can sometimes be a challenge as you need to dig down two or more levels to get to the appropriate dialog box.
There is another way to easily access and change Windows settings: “God Mode” gives you single list of every single Windows setting that you can configure (assuming your IS department hasn’t locked them down).
To give yourself access to God Mode, you need to create a new folder with this exact name (copy and paste the text within the double quotes): “God Mode.{ED7BA470-8E54-465E-825C-99712043E01C}”. The icon for this new folder will look like a small computer screen.
To enter God Mode, click on this folder to GodModeopen it in the same way you would open a regular folder. When it opens you will see 293 settings listed by category. Clicking on any one of the listed items will take you to the dialog box for changing that setting. Take a look at the list – I am reasonably sure most of you will find settings that you didn’t know about. But be careful, changing some of the settings listed in God Mode may render your computer inoperable.

 

When it comes to deleting text we have just typed, most of us will hit the Backspace (PC) or Delete (Mac) key. This works, but deleting text one letter at a time is not the most efficient way to correct backspaceyour mistakes.
Deleting an entire word instead of deleting a single letter is much faster. You can use the Ctrl+Backspace (PC) or Option+Delete keyboard shortcuts to delete the entire word behind the cursor. Remember these shortcuts for faster editing next time you are composing something on your computer.

 

In just the last few weeks, I have talked to two lawyers dealing with hacked email accounts. In the past year or so, I have seen frauds perpetrated where a hacker hacked into a client’s email account and waited until the opportune time (just as a real estate deal closed) to send instructions to the lawyer (pretending to be the client) on where to disburse funds; and a situation where a lawyer’s email account was hacked and the hacker, pretending to be a lawyer, sent instructions to a client on where to send funds. In both cases the funds went to the hacker.
A hacked email account is a big problem, especially if it is the account you use for your practice, and it can be time verydanger consuming to deal with a breach of confidential client information and to get your account back. There is no doubt here, an ounce of prevention is worth a pound of cure.
On top of not falling for a phishing scam in the first place (giving your password to the hacker by answering an email that appears to come from someone you know and trust– see this LAWPRO Magazine article for more details on phishing scams and how to avoid them), there are a number of fairly simple steps you can take to help prevent unauthorized access to your Gmail and to secure your account if it was recently compromised. This excellent checklist from Google walks you through these steps. And while the advice here is specific to Google, you can apply most of the steps to other types of email accounts.
Please review this checklist and take the steps to lock-down and protect your Gmail account. Ten or fifteen minutes of work can make your account much more secure and save you hours and hours of unnecessary work.

 

If you frequently dial someone who as at extension, it can be frustrating to wait for the main phone number to connect before you can to enter the extension number, if you can even remember it. As Canadians, we frequently have to enter a number to select English or French before we can dial the extension. What a pain!
You can easily avoid this frustration can with a comma or two. Simply enter the main contact number as you normally would, but then add a comma followed by the extension number (e.g., 4165551234,567). The comma causes a pause of about 2 seconds. If you need a longer pause, enter additional commas. You can do this for multiple extension choices (e.g., 4165551234,,1,567). This works on most types of cell phones and smartphones.

 

Photos are a major part of the profile pages on all the major social media sites. There are profile photos (usually your picture) and header or cover photos (the big photo that usually appears across the top of your profile or homepage).
As these photos play a big part in making a good impression with people, you want them to look good. But making this happen can be frustrating. Sometimes the photo you post will be scaled to fit in the allotted box with the result that the contents look blurry or distorted, and sometimes parts of a picture are cut off. On other occasions important parts of your cover photo are hidden behind your profile picture.
How do you prevent these problems? It is very easy – simply post photos that match the Social Media Image Size Cheat Sheet dimensions for the particular type of photo you are posting. But every social media site is different – how do you remember all these dimensions? Use the handy infographic posted on the Mainstreethost blog: the Social Media Image Size Cheat Sheet gives you the dimensions for the various photos on the various social media sites. It even gives you the dimensions for the parts of a cover photo that will be hidden behind a profile picture. Before you post a picture, use a photo editor to resize and/or crop it to the appropriate dimensions and it will display exactly the way you want it to.