Category Archives: Excel

Excel

London Coronavirus Heatmap in Excel

Leave a comment

London Coronavirus Heatmap in Excel

Check how many confirmed cases in London borough in Excel tracker?

Whether we like it or not, for some time we have been witnessing spreading of the virus all over the world. Being a Londoner, I have decided to develop data visualisation regarding virus cases observed in one of the largest agglomerations in the world. To do so, I used an MS Excel spread sheet. Heat map presentation is automatically updated on the day-to-day basis and it presents confirmed virus cases in all London boroughs. What is more, using one of my spreadsheets you can follow development of infection over the course of time in 10 most exposed boroughs. Source data acquired is published online by Public-Health England.

Should you wish the attached file to download data from the Internet automatically, you must accept Connect Access Web and Enabled Content.

Then, each time you open the file, the spreadsheet will communicate with www.eexcel.co.uk/coronavirus to updated the current data. Please bear in mind that the connection requires some time and the following message may be displayed in the bottom left corner of the spread sheet status bar: if you see this message, please do not use the spread sheet.

 

Download London Coronavirus Heatmap in Excel by clicking button below:

Download Free

education, Excel

Excel Named Range

Leave a comment

Excel Named Range

Defining names in Excel involves giving a range of cells a unique name, which means that you can easily refer to a specific defined range, e.g. while creating a function. This is especially applicable in the following functions:
as the first argument
MATCH as the second argument
VLOOKUP and HLOOKUP as the second argument.
Formulas containing names instead of ranges are more understandable and easier to use, provided that the names we give will be legible. These names are also used in VBA programming.
In the Formulas card you will find the Defined names section:

How to define name for the range?

• Select the relevant range.
• In the Name Box, enter the unique name of the selected range. Do not use any illegal special characters, e.g. spaces, $, @, #,! or arithmetic operators (+, -, /, *, ^), comparison (<> =) and the text operator (&).
• Accept by pressing the Enter key.

Instead of the Name box you can click Formulas card then Define Name, set up: the name, scope, comment and refers to. In addition, the defined name cannot only refer to a cell or range of cells, but it also contains a formula or a specific value or text.

Create multiple names from the selection

• Select a range of data which contains multiple columns or rows with the labels at the beginning or the end.
• Click Formulas card then Create form Selection or shortcuts[Ctr] + [Shift] + [F3].
• Select options based on what you want to name.

Name Manager

• Click Formulas card then Name Manager [Ctrl] + [F3].
• Create another new name, edit existing name, delete it, filter it and sort it

Insert the defined name into the formula

Simple click [F3] to obtain a list of created names and select the relevant one or start typing existing name and select the relevant one.

You can learn similar tips and tricks in Excel at a comprehensive Training in London.