Author: Gary Knott

But surely you need to use both?

Yes and no… some tasks can only (efficiently) be performed by one or the other. For example, data entry: a keyboard is the obvious tool of choice. But you may be surprised at how many tasks can be completed be either. Typically, we are talking here about marking and amending blocks of data, inserting rows or columns, copy and pasting etc. All tasks commonly performed when working in Excel.

Why does it matter?

You may be surprised how much more efficiently you can work with the appropriate tool. Spoiler alert… at this stage I can reveal that I am a big fan of keyboard shortcuts which can dramatically reduce the time needed to perform especially simple tasks like copy and paste. Added up over the course of a working day, these small savings of time add up and can result in you simply being able to get more work done! And all for a little effort required in learning to use shortcuts.

What do you mean “best to use”?

Before we dive in, let’s be clear about what we mean by “best to use”. It is the user that uses these tools, so we need to consider him or her… therefore I would define this to mean most appropriate and most efficient for the user.

What are the options?

To make my analysis comprehensive, I have considered two types of mouse – the mousepad you usually get with a laptop nowadays and a separate, “old school” mouse – as well as two types of keyboard – again, the one you get with a laptop and a separate keyboard, the type you have to use if you are using a desktop PC and which is optional when using a laptop.

Tool	😊 Advantages	☹ Disadvantages
Laptop mousepad	Can be used on a plane or train, where the table is small and there is no room to use a separate mouse Hard to forget… if you have your laptop with you on a business trip, then you also have your mouse!	I find them extremely inefficient to use; to move the mouse pointer any length over the screen requires multiple touchpad strokes; only experienced users can hope to come anywhere near the efficiency of the other tools For non-users, must be turned off to avoid “ghost working” (accidental activity caused be inadvertently touching the touchpad)
Separate mouse	Generally, much more efficient than the laptop mouse Enables menus and icons to be easily used Particularly good for inexperienced users – easy to use and no need to remember keyboard shortcuts	Often not as efficient as keyboard shortcuts for frequent tasks such as copy & paste (Ctrl C, Ctrl V) Usage requires more space on your desk or work surface
Laptop keyboard	Compact – does not take up much space You can use the full gamut of keyboard shortcuts, albeit this can be somewhat awkward (see disadvantages) No need to research and buy a separate keyboard	Compact, so functions are often combined into single keys, thus requiring laborious (dare I say tortuous?) key combinations to get what you want, e.g. using the function keys such as F2 edit where you often have to press a “function-enabling key” as well as the function key itself… yuk! No separate number keypad to enter numbers, which I tend to do a lot in Excel!
Separate keyboard	You can use the full gamut of keyboard shortcuts… yahoo! A real boon to all who know them or learn some of them! You usually get a separate number keypad to enter numbers; a common task when using Excel Function keys can usually be operated on their own without the need to press any “function-enabling key” (sadly not always the case)	Tasks up space on the desk; IMHO this is more offset by the advantages More space means more keys and sadly a higher risk of additional keys with functions which I never use and/or which do not always work (media start/stop, home, arrange Windows, settings, picture etc) I am such a fan, I will sometimes take my separate keyboard to meetings where I expect to have to do a lot of computer work; this is then something extra to transport

To sum up, many common tasks in Excel can be performed by either mouse or keyboard, so you have a choice. I recommend the keyboard – ideally a standalone keyboard - so that you can use time-saving shortcuts. You can download a list of the best shortcuts under downloads.

https://www.how2excel.com/en/downloads-2/

This is A4 in size, but you can fold it in half in the middle to have A5 size with two sides if that suits you better. The best way to learn these is "little and often". Pick one or two new shortcuts each week and use them as often as you can to make them second nature. Some people like to use a highlighter pen to mark the ones they have learnt to show progress and aid motivation.

Have fun!

I hope you are also a fan of SUMIFS… this great function can be extremely useful for adding up sub-sets of data. But it has two limitations:

1. Performing calculations on data in other files is not best practice, but it can be a practical solution in some cases. In such cases, a SUMIFS formula can generate errors if the other file is closed.
2. You cannot have criteria in two dimensions (rows AND columns), just one or the other.

There is a way to solve both these issues using the function SUMPRODUCT.

1. SUMPRODUCT in one dimension

Here is an example using small table of data.

As you can see, the formula is like the equivalent SUMIFS formula. Here, however, each criteria range is not separated from its related criteria by a separator (typically a comma or semicolon, depending on your regional settings). Instead, they are separated by an equals sign, which I find easier to read. Each (criteria range = criteria) test is surrounded by brackets and multiplied with the next (criteria range = criteria) test. At the end, you must multiply by one to convert all the true and false results into 1s and 0s and lastly you multiply by the data range which is to be summed. With SUMIF (1 criterion), this data range must be the first argument in the brackets, with SUMIFS (1+ criteria), it must be the last. With SUMPRODUCT, it can be either first or last.

External files

All the function arguments (the bits in the brackets) can refer to an external file.

how2tip: I find it easier to write and test such a formula in the file with the source data and then cut and paste it to the desired location. This is easier because you can (a) better see what you are referring to and (b) the path and file name do not appear in the formula until you cut and paste it, so it is easier to write, understand and test.

2. SUMPRODUCT in two dimensions

The above principles can now be extended to two dimensions, as shown here, using the same data table.

Here it is important that (similar to the one dimension example) the criteria ranges match the height or width of the data table, as you can see in the F2 (“edit mode”) screenshot above.

3. Conditional formatting

To put the icing on the cake, I decided to use conditional formatting to automatically highlight (in green) the data cells being added up. These vary, depending upon what criteria are selected in the salmon-coloured input cells.

The conditional format must be applied to the all data cells in the table since any of them could fulfil the selected conditions.

The format must highlight cells (in this case in green) if all conditions in the SUMPRODUCT formula are fulfilled, as shown below. Here it is important to pay attention to the dollar (fixing) symbols. Always write the formula from the viewpoint of the cell in the upper left corner of the data range, here C3. The formula must start with an equals sign (as in all Excel formulas) und must give a true or false result. It is the equivalent of writing an IF formula, but you do not need the IF function. Just write the logical test, using “OR” or “AND” as required.

That’s it, have fun!