10 tips to help you work faster in Excel

Published on October 11, 2017 by in Excel | General

0

    1. Table it! If you have large data sets, convert them to a table. This means that if you scroll down to the bottom, the headings will remain visible at the top
    2. Hide! :Hide the columns/rows you don’t need.
    3. Top row visible: Freeze the first column of your data so that it’s always visible as you scroll across. Use View | Freeze Panes | Freeze Panes for this. Note that your freeze will happen above and to the left of the point you had clicked on before you activated Freeze Panes.
    4. Navigate...Learn the common navigation keyboard shortcuts:
      1. Ctrl and home to always return to A1 – no matter how far you have wandered in the spreadsheet. Think of it as Dorothy’s red slippers
      2. Ctrl and right arrow always brings you to the right hand side of the data set
      3. Ctrl and left arrow always brings you to the left hand side of the data set
      4. Ctrl and arrow down brings you to the bottom of the data set
      5. Ctrl and arrow up brings you to the top of the data set
      6. Ctrl and * allows you to highlight the data set
    5. Instant charting: You can create an instant chart by highlighting the data and pressing F11 (this creates it on a separate sheet). If you want the chart to be on the current sheet, press Alt and F1
    6. Quick Copy down of formula: When you convert your data to a table, and then enter a formula, it is automatically copied down – invaluable if you have a data set with thousands of records.
    7. Learn how to (Format) Paint: Use the Format Painter option to copy your conditional formatting from one area of your workbook to another. You can also use it if you come across formatting that you have to use but do not know how to create it.
    8. Learn the shortcuts for what you use most often: You can use the keyboard shortcuts Ctrl and + to insert rows or columns (highlight the area you want to insert them in first)
    9. Learn how to fit on a page: Make sure you know how to set the printing options so that you can get it to fit on a page rather than it printing across multiple pages and then frantically trying to stick it together. In particular, learn how to use Print Titles so that it makes your report much easier to read. You can check out a tutorial here on printing
    10. Free goodies!: Have a look at the templates available to you under File | New. Very often much of the work has already been done here and they are all free. So they are well worth checking out.

Continue Reading

0

September can be described as the new January and it’s often a time when people decide to start something new or get stuck into something they have been thinking about learning.  Dare I suggest that could be your Excel skills ?In this blog post I am offering 5 ideas around that..

Idea 01 – Know your tendency.
One of the writers I enjoy following most is Gretchen Rubin and she has developed a framework for expectations which she calls the Four Tendencies. If you are curious you can get the overview here  and if you wish, you can take the quiz here... How is this relevant to developing an Excel learning plan? (In the interests of openness, I am an Upholder). It can help you understand what you need to help YOU learn and why what works for someone else doesn’t work for you.
By the way, one type is not “better” than another type. Each has its own strengths and challenges and what’s important is to leverage this.  Gretchen has an excellent post here about habit change strategies for different tendencies because the most effective way to learn anything is to make it a habit…

  1. Well if you are an Obliger (which is one of the most common tendencies), it means you can meet outer obligations but struggle with honouring your inner expectations) you will struggle with following a plan by yourself but joining a group/creating some sort of external accountability will work very well e.g. set up an Excel learning group – and with people who will hold you accountable – your mother doesn’t count, unless of course she is also an Excel learner….
  2. If you are a Rebel (which means you resist outer expectations and inner expectations!)  this means that even if you are interested in learning Excel, you will not do so if someone else or even yourself (!) tells you to do it. In that case you are better off approaching it as something you are *choosing* to do, that being good at Excel is part of your identity, e.g. that developing computer skills gives you freedom.
  3. If you are a Questioner, (you will meet an inner or outer obligation if you can see a good reason to do so – you will tend to do alot of research) and  you will not learn Excel skills unless you see a good reason to do so. Efficiency is important to you. So if you do want to feel motivated find a reason that works for you e.g. it’s going to help me become more efficient, waste less time and keep that reason in front of you.
  4. If you are an Upholder, (you can meet inner and outer expectations) you will have decided to improve your Excel skills and in that case the best thing to do is to draw up a schedule of what you want to learn and tick it off as you go along. I LOVE ticking things off. Sad to say, there are times when I will learn something, just so I can tick it off…

Idea 02 – Set yourself up for success :
If you are working full-time and have a family, you are probably not going to study for 3 hours a night. Instead look at the reality of your situation and identify pockets of time e.g. on the way to work or from work, early morning, evening where you could spend 15 minutes learning something about Excel. The following is a list of things you could learn in 15 minutes. If 15 minutes is too long, identify something you can do in 5 minutes. Identify something, set a timer on your phone and do it.

  1. Learn 3 keyboard shortcuts. Here is one on Ctrl keyboard shortcuts and this is one from DataPig Technologies which combines Excel and the periodic table – what is not to love? Yes, I know…sad, sad, sad..
  2. Learn how to create basic formulas
  3. Find a YouTube video on a topic – just search for the topic you want to brush up on
  4. Learn how to add up numbers using Autosum.
  5. Learn how to sort and filter.

Idea 03 – Understand how you learn best:
Do you learn best by solving problems, by doing an online course, by asking someone else to show you, by figuring it out yourself on Google (God Bless Google :-)). Note that the strategy may very depending on what you want to learn. When I am improving my technical computer skills, I would invariably choose an online course. I am a big fan of Mynda Treacy.   However, when I started learning music, I worked with a tutor simply because I knew so little about it that I didn’t know where to start. I am working on my French so I use a tutor and I also use an app for daily practice (Duolingo or Memrise are both excellent).

Idea 04 – Identify a retention strategy: If you are going to be working with Excel on a daily basis, that in itself will be a retention strategy. However if you only work with it from time to time…these ideas may help. A comment I frequently hear in class is ” this is great, but I don’t know how to retain it”.

Please feel free to add your own ideas in the comments.

  1. Add a comment in the Excel file (click on a cell, right click and Insert Comment – it’s the Excel equivalent of adding a Post it note to the cell) which outlines what your thinking was or what you did.
  2. Record what you did: Windows 10 comes with a free screen recording tool. Here is a link to a tutorial on it. I use Camtasia (which is a paid product) but here is a list of screen recording options. Store those videos together in a folder called Excel Learning and name them as clearly as you can so that you can find them again. One idea would be to create a hyperlink in your file to this video. Here is how you create a hyperlink
  3. Quickly write out a summary of what you did. Just doing this helps retention. Hand write it or type it up in Word and save it with the same name as the file you are using it with.
  4. Describe to someone else what you did – you know when you don’t want to forget something and you tell someone else to remind you – how that automatically helps you remember – that’s what you are leveraging here.

Idea 05 – Leverage the Pareto principle – 80/20 

 Often people say that they don’t know what they don’t know but as someone who has been teaching Excel a long time, here is a list of the topics you *do* need to know.  

Needless to say, it is in an Excel file :-)

If you know these topics, they will cover 80% of what you need in Excel. On the other hand, you may already know from work what you need to know – ask your boss or colleagues about this and you can devise a plan from this.

Let me know what has worked for you – and also maybe what hasn’t worked for you – we can all learn from it

 

Continue Reading

0
http://memes.com/img/1112650

http://memes.com/img/1112650

In a class recently the subject of age calculation came up, which reminds me of when my son was young and would ask me how old I was. “19”, I would say succinctly. He was at the age when he was learning basic maths. “So what would make you 12 when you had me..” “Yes, what can I say..I was a child bride”…

Of course as he got older that response met with a snort of derision and a muttered comment about what life in the Dark Ages before computers and the Internet…

However, back to the class. I offered two solutions to this dilemma. In both cases, because the formula uses today() – which uses…yes, you guessed it, today’s date…the age will always be current.

One of them used the Int() function (which is to just show the whole number part of an answer – assuming that you want your answer as 12 as opposed to 12 and a half.

In the second approach we used an undocumented Excel function (you won’t find it in Excel functions) called DateDif to show the difference. Both are illustrated here.

I’m in a hurry…I just want to see the file

In that case, you can download the completed file here.

I’d like to see how it’s done

Approach 1 – Using the Integer function.

In this video I show you how to use the Today() function with the Int() function to calculate the current age.

Approach 2 – Using the lesser known DateDif() function

This is an undocumented function in Excel and has some issues but it could be an option. You can read more about this function here.

DATEDIF(start_date,end_date,unit)

Start_date = Date of Birth

End_date= Today()

Unit = “Y”

We are going to work through the same set of dates and we will end up with the same set of answers.

The video shows you how it’s done.

 

 

Continue Reading