Tag Archives: Excel



xlookup vs vlookup

Forget about the VLOOKUP function. The new XLOOKUP function has arrived, being a genuine alternative for VLOOKUP, HLOOKUP, INDEX and MATCH. If you know how to use the XLOOKUP function, you will never bother to use VLOOKUP anymore. The new feature, which has been recently introduced, has more options and is characterised by its flexibility, efficiency and user-friendliness.

XLOOKUP has six arguments:


1. Lookup_Value
2. Lookup_Array
3. Return_Array
4. [If_not_Found] – #N/A
5. [Match_Mode] – default FALSE – Exact match
6. [Search_Mode] – default TRUE – First to last

Benefits of XLOOKUP over VLOOKUP:
✅ the searched result in the column may be placed to the left of the column with the searched value;

✅ change of the structure of the table with data by inserting or removing columns will not affect the result;

✅ we select only relevant ranges and not necessarily the entire table;

✅ it can return a range, not just a value;

✅ it can return a value, if not found;

✅ the exact search is set to 0 by default in argument 4, the most commonly used option;

✅ it can return the nearest higher value if we declare (1) in the 4th argument;

✅ should we look for the closest value in the 4th argument we do not need to have any data sorted;

✅ wildcard support only if you declare (2) in the 4th argument;

✅ it can search for the last value (-1) in argument 5;

✅ it carries out binary search;

✅ it performs searches not only vertically, but also horizontally if we mark the ranges accordingly;

✅ faster functionality.

You can learn similar issues and tricks at my comprehensive Excel Training in London.

A Multiplication Table in EXCEL

A Multiplication Table in EXCEL

Create a multiplication table in Excel

Let me give you some different ways to handle formulas. Firstly, using the so-called Table formulas by completing the edited formula in the selected range. Secondly, I will provide you with a lot of keyboard shortcuts, which significantly facilitates and accelerates our work. All above makes us more effective and thus more competitive, e.g. on the labour market.

Construction of the multiplication table

In this example, we are creating a multiplication table with a sheet at the top left corner.

1. Vertical insertion

Fill a column with successive numbers from 1 to 10 in rows starting from cell A2.
a) each number individually or
b) insert 1 into A2 and 2 into A3, then select both cells, and then, by dragging the mouse in the lower right corner of cell A3 up to cell A11, fill the remaining cells automatically with successive numbers
c) you can also use the quick fill method by selecting the tab
• Home, in the Editing section, select the Fill and Series icons

2. Horizontal insertion
Analogously, we input 10 numbers, but this time horizontally starting from cell B1 to K1
a) You can use a similar method as above, instead of Columns, select Rows.
b) Alternatively, if you have already inserted a sequence of numbers vertically, you can use it horizontally:
• check the created range of numbers A2: A11;
• copy CTRL + C to the clipboard;
• from cell B1 paste as Transposition
Paste Special / Transpose.
c) In addition, there is one more way to insert a transposed number, rigidly linked with each other using an array formula.
• first select the range B1: K1, in which you want to enter the formula.
• in the function brackets as an argument we put the range we want to copy = TRANSPOSE (A2: A11)
• and we accept CTRL + SHIFT + ENTER
• then select Columns, Step value: 1, Stop Value: 10 and OK

Solution of the multiplication table

a) Array formulas

• Select range B2:K11
• Insert formula =A2:A11*B1:K1
• Use shortcuts CTRL + SHIFT + ENTER


b) Formulas mixed cell reference

• Select range B2:K11
• Insert formula  =$A2*B$1
• Use shortcuts CTRL + ENTER

Formatting the multiplication table

• Select the entire table CTR + *
• Home> Conditional Formatting> Colour scales and select 2 options, which will cause the highest values to turn red, the lowest to green and between the intermediate colours.

Excel spreadsheet download

Download the sample file containing the solutions here.

Download Free

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

Excel World Cup Russia 2018 Sweepstake

Excel World Cup Russia 2018 Sweepstake

Excel World Cup Russia 2018 Sweepstake
It is time to talk about the global event that connects the entire world to one platform. The long-awaited football series is scheduled to begin on the 14 of June 2018, and it will end on the 15th of July 2018. The true football or soccer lovers cannot afford to miss the event. Teams from almost every continent will take part in this global event so fans cannot take a risk to miss the wonderful battle series. Many game lovers are wondering about who is going to win this global event by chasing the top scores. It is hard to finish the battle that reflects the scenario of a clash of the Titans.

I have also provided an interactive free spreadsheet for the Excel World Cup Russia 2018 Sweepstake, likewise, as the last European Championship in football in 2016. Please check my previous football Excel project below.

Euro Cup 2016 Excel Match Schedule

The predictor spreadsheet has been created by professionals to assist enthusiasts in the easy analysis of match scores. All the essential details are well highlighted in the schedule. In this spreadsheet, the Excel WorldCup Russia 2018 details are listed accurately. The spreadsheet has been created without the use of a VBA code and contains all the fixtures, schedule, dates and time of the match. I have also added options for selecting the time zone and language. After fill in the scores between the teams in bookings boxes, the spreadsheet automatically calculates the goals, updates the data in the table, sorts the table according to the Rules of FIFA and assigns a team to the next stage of the competition at the right time and place.

Tournament roles

32 teams are going to take part in this event in 2018 and they are presently divided into 8 groups. The runner-ups and winners will get a chance to move to the next level of the game to face the new challenge in the game battlefield. All players might be practicing hard to present their country with huge potential and each one of them must be dreaming about the victory. But only the strongest one will come out as the top ranker in WorldCup Russia 2018. The first 16 winners will be able to reach the quarter-final of the match and the winners at this round will get the opportunity to fight for the semi-final. The fight will continue to the final round and ultimately we will see the winner who will own the wonderful golden trophy.

The ranking of each team in each group will be determined as follows:

  • Greater number of points in all group matches.
  • Goal difference in all group matches.
  • Greater number of goals scored in all group matches.
  • Greatest number of points in matches between tied teams.
  • Goal difference in matches between tied teams.
  • Greatest number of goals scored in matches between tied teams.
  • ranking FIFA.
  • Drawing of lots by the FIFA Organising Committee.

News in current version Excel World Cup Russia 2018 Sweepstake

  • Sweepstake or office pool for 20 players, including dashboards.
  • Select and highlight your favourite team.
  • Selection of Languages options: English, Polish, Russian, Spanish and German.

Sweepstake or office poll

Rules of Sweepstake created in spreadsheet.

The user fills the sheet in, only using the yellow boxes, players’ names and types of results of individual players for matches.  The results of matches (grey boxes) are taken from the worksheet 2018. The system formula counts the number of points each player earned in each of the matches based on the following rules.
1.  If the player correctly predicted the exact outcome of the match, (Result and Score) they will receive 3 points.
2.  If the player correctly predicted the winner of the match or a draw, they will earn 1 point
3.  In any other case, the player will receive 0 points.
4.  If multiple players finish on the same number of points, there finishing order in the table will be decided based on whichever of the players predicted an accurate result the most times.
5.  In other cases, players will take the same position.
6.  These rules are automatically implemented; therefore, the table will sum up the results of all the players.

This full guide about games can help you to visualise game progress at every next stage without losing any essential information. Prefer to check the schedule on time to get appropriate insights about the game.

How to share Excel file for multiple users and co-author a shared workbook

FREE Excel World Cup Russia 2018 Sweepstake Template download

Please feel free to download Excel World Cup 2018 Sweepstake Predictor Match Schedule template. It does not require any additional software, or any registration and it is also free of VBA code. Just push the button below, save the file and enjoy supporting your team and keep calm during the contest if it is possible.

Download Free

If you are interested get an advanced version Excel World Cup Russia 2018 PRO, Sweepstake for 20 players or more  & dashboards, automated diagram visualisation with flags and special access to advanced functions and methods used in this spreadsheet including full access to other spreadsheets, please visit the shop, button below.

Download Free

на русский язык Polska wersja