Category: Blog EN

This is the final result...

I will show you how to develop this in just 4 steps.

Step 1 - Paint your tree

Make columns narrow, add background colours then add a star using the character map and the font Wingdings.

Paste into the cell above the tree, it looks like this «.

Then change the font, size and colour of the "star cell" so it looks like a star.

Step 2 - Add formulas

Enter this formula in all tree cells:

=RANDBETWEEN(1,3)

This generates a random number between 1 and 3 every time a calculation is performed.
We will use these numbers to set the colours of the lights.

Step 3 - Format

Next add conditional formatting to all tree cells as follows.

Then "Manage Rules"...

If you also want to animate the star, add 3 conditional formats to the star cell as follows.
Select the star cell, then Conditional Formatting, New Rule…

Select "Use a formula…" and set the font colour based upon the value in a tree cell (say P8).

Repeat for the other two possible values with different font colours.

You can remove gridlines if you want a "cleaner" look for your sheet.

Step 4 - Animate

Press F9 to change the colours.  Or press and hold F9 to animate.

You can download the file with instructions under downloads.

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

Enjoy!

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!

You can work more efficiently in Excel if you can navigate effectively. With regular practice of the methods I explain below, you too can become an expert in Excel navigation!

Navigation in and around Excel can be divided into three main task areas. You can perform all three types of task with the mouse, but using the keyboard is usually more efficient, largely because you need to perform less steps than with the mouse.

1. Navigate between open applications / documents

• Alt + Tab --> cycle between all open applications / documents

• Windows key + Tab --> shows all open applications / documents. Select one with the mouse or use the arrow keys to highlight the desired application / document and then press enter to select it

• Ctrl + Tab (in Excel) --> cycle between all open workbooks
   

2. Navigate between worksheets

• Ctrl + Page Up or Down --> move from one worksheet to the next

• (Mouse) Use the arrow buttons in the lower left corner of Excel  to shift the worksheet tabs in view; Press Ctrl at the same time to show the first or last worksheets

• Right-click on these arrow buttons to get a list of all worksheets in the model, click to jump

• If you have my toolKCit installed, use the button “List all sheet names” to create a new sheet with easy-to-use hypertext links
  
  Tip: my toolKCit contains this and 18 other useful tools - buy it at eloquens and use the discount code HOW2EXCEL to get 50% off

• Use range names:
  • Define: select cell(s) then type a range name, without blanks, in the name box below the toolbar e.g.
    
  • Use: Click on the name box down arrow, click on a name to jump there
     

3. Navigate within a worksheet

• Ctrl + Home --> select “home cell”

• Ctrl + End --> select “end cell”; if this is wrong i.e. includes many blank rows or columns then delete these and save the file to reduce the file size and possibly improve the calculation speed

• Alt + Page Up or Down --> move left or right, a screen at a time

• Use the arrow keys

Arrow key alone	With Ctrl	With Shift	With Ctrl + Shift
Move to the next cell	Move to the end of a cell block or to the next non-empty cell	Move and mark cells	Move in a block and also mark the cell range

• Ctrl + A or Ctrl + * --> select a complete block of cells

• You can also use many of these tips in Word
   

Have fun navigating!

PS. You can download this blog as a handy PDF here
Navigate_efficiently_in_Excel

Excel offers a huge variety of charts (also commonly called graphs) so it can be difficult to identify the most suitable type for presenting your data. Here I explain some basic guidelines which should help you pick the best type in several typical cases and some useful tips. I also give some advice on which chart types are not recommended.

What’s on offer?

At the time of writing, Excel 365 offers 17 different chart types, shown below. Each type has several sub-types, shown below for column (bar chart) and line chart. You can also mix and match different types in a single chart, now called “Combo”. That means a lot of choice.

Does Excel offer any help?

Yes - when you select data and move to Insert, Charts, Excel offers some recommended charts which you can review.

If you hover over a chart icon, Excel shows you a large tip box with guidance.

I don’t always agree with the recommended chart types or the tips given, however, so I prefer to select my own using the following principles.

What type of data do you have?

I have reviewed the charts in numerous spreadsheets and found that in over 90% of cases these fall into one of the following three categories, so I will focus on these.

1. Data over time
2. Data per category
3. Change in a single value over time or between e.g. plan and actual

1. Data over time

E.g. Sales per year or number of kilometres travelled per month

--> In this case I recommend a line chart as it is easier to see trends over time.

In the two charts above I have plotted the same data as both a line chart and a clustered bar chart. The line chart shows more clearly show that business A is a rising star, B is plodding along, and C is a falling star. In the clustered bar chart, this development is more difficult to see. In my opinion, this is more important than comparing businesses within each year, which is what the clustered bar chart enables you to do.

2. Data per category

E.g. number of customers per region or average amount spent in a month on various leisure time activities (such as going to the cinema) in different countries.

--> In this case I recommend a clustered or stacked bar chart to facilitate comparisons. There are several options, which I now compare.

In the four charts above I have plotted the same data as clustered charts (1 and 2) and as stacked bar charts (3 and 4).

• Charts 1 and 3 are better to analyse the regions:
  (1) Here you can compare the businesses within each region
  (3) Here you can better compare regions against each other

• Charts 2 and 4 are better to analyse the businesses:
  (2) Here you can compare the regions within each business
  (4) Here you can better compare businesses against each other

• Ask yourself which chart version best presents the data for the analysis you are performing.
   

How2tips:

• In charts 3 and 4, I have added data labels as it is no longer easy to judge the size of each bar component; to do this, right-click on a coloured bar and select “Add data labels”; repeat for all other colours. Light coloured labels are better on dark backgrounds and vice-versa. To format labels, simply left-click on a label to select the series and then use the usual text formatting icons in the Home ribbon or right-click on a label and select format data labels.
   
• In charts 3 and 4, I have also moved the legend to the right-hand side. This matches the construction of the bars (e.g. in chart 4, from North at the bottom to West at the top) and so makes it easier to read what each colour in the bar represents. To do this, right-click on the legend, select "format legend" and then select legend position "right".
   
• In charts 1 and 2, it is better to have the legend at the bottom for similar reasons.
   
• You can change the chart type by right-clicking on the chart and selecting the aptly named “Change chart type.”
   

If some of your data is not split into discrete categories such as business regions but can instead take a wide range of values such as price or age, then you display your data grouped into clusters in a histogram. Examples here could be total sales of wine in different price categories or ice cream consumption per age group. A histogram is similar to a bar chart but each bar represents a given range of values such as wine priced up to €5 per bottle, €5 to €10 per bottle etc.

3. Change in a single value over time or between e.g. plan and actual

If you want to show how a certain value (typically sales or profit) changed from one year to the next, you can use a waterfall chart to show the changes, as shown above. This chart type is only available as standard from Excel 2016. In older versions, you must create it yourself using stacked bar charts, with some bars having no colour.

Assuming you have Excel 2016 or later…

1. Set up your data with the starting and end values and the changes between them with a suitable short description in each case.
2. Select your data, then go to Insert, Charts, Waterfall
3. Select the first bar by clicking it (so only the first bar is selected) and in the "Format data point” dialog that appears select “Set as total”
4. Repeat for the final bar
5. Optionally select and change the colour of other bars if you don’t like the defaults

Conclusion: This chart type can be very useful, but it is not as flexible as other chart types e.g., changing the colours is not easy.

More details: see Microsoft or xelplus (Leila Gharani)

What if I have other data types or the line and bar charts don’t help me to tell my story?

• Here you should think about what you want to convey and try out different chart types to see which best does the job.
   

How2tips:

• If you are not convinced with one chart type, you can change the chart type, as noted above.
   
• Alternatively, you can copy and paste the chart and change the chart type of the second version so that you can compare chart types side by side. I did this for the four bar chart examples above.
   
• Please remember that a simple table may be clearer than a chart, especially an overloaded or unusual chart.
   

Are any chart types not to be recommended?

• Pie charts – this chart type is well loved but the data can always be presented in a better way, perhaps as a simple statement or table. For example, if you have 60% of people are in favour of X and 40% are against, this simple statement is all you need; a pie chart does not improve understanding.
   
• 3D – these often make it hard to read the values and the 3D effects distract from the data, which, presented in the best way, should speak for itself without gimmicks.