productivity

#8 Excel Tip a Day: Working with Tabs

#8 Excel Tip a Day: Working with Tabs

Here is tip #8 in a series of an Excel Tip a Day for December 2015 – Working with Tabs . Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

This tip is pretty obvious, but you’ll probably recognise that you have failed to do it in the past.

If you’re working with a spreadsheet that has a large number of tabs, you’ll often be jumping from one tab to another. Rather than using the scroll bar to do so, you can just temporarily drag the tabs close to eachother. When you are done working across those two tabs, drag the temporarily relocated tab back to where it came from. Simple, but a good little productivity hack.

I hope this was of some assistance to you!

#7 Excel Tip a Day: Display your numbers as millions

#7 Excel Tip a Day: Display your numbers as millions

Here is tip #7 in a series of an Excel Tip a Day for December 2015 – Display your numbers as millions. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

If you work with spreadsheets with large numbers (we all love that, right?), it is often unsightly to show those numbers in full. “$12,158,168.05″ really doesn’t say any more than “$12.16m”, particularly on an output sheet. In order to change $12,158,168.05 to $12.16m, follow these steps:

  1. Select the cell(s) you wish to format.
  2. On the Home ribbon, press the little down arrow in the bottom right of the number format window. It’s the one with “Currency” at the top.
  3. In the Category list on the Number tab, choose Custom.
  4. In the text box under the word “Type:”, type in $0.00,,”m”

Done! If you want more or less decimals, add or subtract 0’s after the decimal in the above format.

I hope you find this helpful!

#6 Tip a Day: Multi-format within a cell

Here is tip # 4 in a series of an Excel Tip a Day for December 2015 – Multi-format within a cell. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

Occasionally, you may have a desire to format a text string with different formats for different parts of the string. Like this:

CaptureIt is actually quite easy to do.

  1. Type your text.
  2. Double click on the cell.
  3. Choose the subset of the text string you would like to format. You can format this text independently of the other text in the cell.
  4. Repeat for the other subsets of the text string, as desired.

I hop you find this helpful.

#5 Tip a Day: F4 – repeat action

Ok, so this one is a quick one. We are on the 5th day of the Excel Tip a Day for December 2015.

Forever, I have been using the format painter to copy the formats of cells, but I recently learned there is a better way!

If you format a cell in a way that you want to copy, you simply have to highlight the cells you want to copy the format to and hit F4. That should save you some clicking!

Hope you find this useful!

#4 Tip a Day: Colours in your workbook

Here is tip # 4 in a series of an Excel Tip a Day for December 2015. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

I come across many spreadsheets created by others that look like they were taking acid when they put them together… So may colours! Having cells coloured is a good idea, providing one thing:

The colours have to mean something.

In order to mean something, those colours should be spelt out on a control sheet, such as this:

colours helper

 

 

 

 

This small addition will help anyone who picks up your spreadsheet to determine what is going on. It should also make your life easier – you don’t have to think about the colours you are going to use, they are set up front based on the reason you are colouring the cell. If there is no reason – don’t colour it!

#3 Tip a day: Excel Camera Tool

#3 Tip a day: Excel Camera Tool

This tip is # 3 in a series of a Tip a Day for December 2015. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

If you’re ever put a spreadsheet together that has an output or report page, you might have come up against a situation where things just didn’t look right. I guess it is one of the downsides of the Excel grid, you are somewhat constrained in your capacity to place things where you want them – or are you?

The Camera tool allows you to highlight a range and then take a photo of that range to create a picture that updates automatically as the information in the range changes. You can place the picture anywhere on a page and it will display that information independent of the formatting of the cells below the picture. Fundamentally, you are no longer constrained by the width/height of cells when trying to lay out your spreadsheet.

So how do you get the Camera tool?

  1. Right click on the ribbon.
  2. Choose “Customize the Ribbon…
  3. Change “Choose commands from:” to All Commands.
  4. In the list below, you will find Camera.
  5. Select it and add it to the ribbon you would like to use it in.
  6. Highlight an area, then hit the camera icon you just added to the ribbon. You’re away!

I hope you can use this to remove the restrictions of columns and rows!

#2 Tip a Day: Ctrl + 0

#2 Tip a Day: Ctrl + 0

This tip is # 2 in a series of a Tip a Day for December 2015. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

If you’re working with spreadsheets that stretch out to the right beyond the current frame, chances are you jump out to the right a fair bit. Usually, this is done by positioning the active cell on a row with data, using control->arrow to jump to the end of the row’s data.

To save a little time, if you go one column to the right of your most right-hand data, control->arrow to the right and then press Ctrl+0, all columns to the right will be hidden. They’re blank anyway, so you don’t need to hide them. Now, you can get to the most right-hand column from anywhere, not just rows with data.

This tip will only save you small amounts of time per use. But over time, those time savings will add up!

P.S. You can also do the same with the rows below the bottom used cell using Ctrl+9

#1 Tip a Day: Prevent Excel Automatically Creating Hyperlinks

#1 Tip a Day: Prevent Excel Automatically Creating Hyperlinks

This tip is # 1 in a series of a Tip a Day for December 2015. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

Does it annoy you when Excel automatically creates hyperlinks to anything that looks like a web address? When you first hit enter on the cell after inputting the address, you can press Ctrl+z to undo the insertion of the hyperlink. However, if you’re after more than a piecemeal approach, and want Excel to stop doing it all together, here’s how you do it.

  1. Go to File -> Options -> Proofing
  2. Click the “AutoCorrect Options…” button
  3. Go to the “AutoFormat As You Type” tab
  4. Untick the top radio box: “Internet and network paths with hyperlinks

Note, this tip also works with Word!

Excel System to send many customised emails

Excel System to send many customised emails

I, like most people these days, receive a truck load of emails. Many come from banks and are mostly addressed to “Hi”. There are plenty of posts on the internet about how to get engagement with your emails and increase open rates. Apparently, but unsurprisingly, making the recipient feel as though you have sent the email to them alone (i.e. personalising the email) is a great way to increase engagement.

Recently, I participated in the Cancer Council’s Relay For Life. Our team joined a little late, and I was travelling and under a bit of pressure at work which meant that leading into the final week the amount of money I raised was paltry. In fact, I had “raised” a total of $100. When I say “raised”, I am being a little disingenuous – I was the one who made the donation!

So I decided to take the time-poor approach to raising funds. Spam my LinkedIn contacts. I rarely ask anyone for anything (except James Waddell), so I thought it would be ok for me to hit them all up for a donation. At best I would raise a bit of money and potentially reconnect with a bunch of people who I hadn’t spoken to in a while. At worst, I would piss some people off for a good cause. If you receive an individualised email it’s not spam, surely?

A few years ago I wrote a VBA program for a friend at UBS to enable him to email his growing list of clients with a personalised greeting. As an avid reader of his excellent overnight recap, I wanted the email to be addressed to me rather than “Happy Friday”. So I dusted off the old spreadsheet, updated the code to enable it to insert the signature, dropped in my LinkedIn contacts and Boom! Individualised emails addressed to my LinkedIn contact list. Very efficient.

So, how did this approach go? Well, in 1 week my fundraising total rose from $100 to $2,500. Not bad! Hitting the “Go” button on my LinkedIn list is literally all I did. I also managed to reconnect with a bunch of old colleagues and acquaintances, so it was a double win!

If you’d like a copy of the file, just sign up on the popup, or contact me. As always, please use it for good and not evil.

 

3 steps to never forgetting anything ever again

Forgetting stuff sucks. As we are all busy, busy, busy, it becomes easier and easier to forget things. No one ever puts their hands up to remind you that you have forgotten to follow them up on something that you asked for. They have probably forgotten about it also. I am probably pretty frustrating to deal with, because I am quite persistent and I never forget to follow up anything. Ever.

Here is my secret to never forgetting anything ever again, in 3 easy steps:

  1. Follow this link Followup.cc ;
  2. Sign up; then
  3. Use it, religiously.

Seriously, it has to be one of the easiest apps to use, and it is one of the few apps I consider useful enough to put my hard earned down on. I use heaps of different apps, and try out many more, but Followup.cc is one of my favourites. Here are a couple of ways I use it:

  • I email you, asking you to provide me something by a Monday at 4pm. In the BCC field, I include mon-4pm@followup.cc. On Monday at 4pm, I get an email reminder. If you haven’t sent through what I requested, guess who gets a follow up at 4:01pm. Yep, you.
  • If I need to do something at a specific time, I just shoot a quick email message off with the subject being what I need to do. I walk home from work, so I am often struck with inspiration or other random thoughts. If I realise I need to do something, for instance, a quick email to tomorrow@followup.cc and tomorrow morning when I arrive at work I have a reminder in my inbox.

One thing to note is that as you are effectively sending an email to a third party app, you should be careful about what you’re sending. I never BCC on sensitive or confidential emails. I just shoot a quick followup.cc email off after the initial email with the person’s name, and a quick note to job my memory. Of course, the reality is that we shouldn’t send anything confidential by email… but let’s be real – the world would grind to a halt!

Anyway, followup.cc. I guess it isn’t my little secret anymore.

Hope it helps!

Cale Bennett.