Tag Archives: Excel

A Multiplication Table in EXCEL

A Multiplication Table in EXCEL

Create a multiplication table in Excel

By the way, I’ll give you different ways to handle formulas: first of all in ranges using the so-called Table formulas or a keyboard shortcut by completing the edited formula in the selected range. In addition, I will give a lot of keyboard shortcuts, which significantly facilitate and accelerate our work, which makes us become more effective and thus more competitive, even on the labour market.
Construction of the multiplication table
Let’s assume we’re creating a multiplication table with a sheet at the top in the left corner.
1. Vertical insertion
Put vertically in succession numbers from 1 to 10 in rows starting from cell A2.
a) each number individually
b) or 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 put 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 entire world to one platform. The long-awaited football series is scheduled to begin on 14 of June 2018, and it will end on 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

Top 16 Excel Double Click Mouse Tricks

Excel Double Click Mouse

Excel Double Click Mouse

Excel Double Click Mouse could be powerful tool for Excel users. It saves your time and it’s easier to use compare to other method. Therefore, I decided to share my top 16 Excel Double Click Mouse including Excel shortcuts.

#1. Skip the OK button

Save time by skipping extra step. For example when you are applying cell formats, double click on the required format to skip the OK button.
Skip the OK button

#2. Collapse / Expand Ribbon by Clicking on any Tab Names

To to get more space on screen and show more rows in your excel worksheet, double click on the active tab or use the shortcut [Ctrl]+[F1]. Collapse the Ribbon
You can also permanently expand hidden ribbon back if you double click on any tab names.

#3. Rename Sheet

Instead of following the long process of right clicking and renaming a worksheet, just double click it. This enables you to edit the name or use the keyboard shortcut [Alt]+[O][H][R].
Rename Sheet

#4. Lock the Format Painter ON

If you want to apply certain formats on a number of non-continuous rows/columns, lock the format painter.

    • Select the section with the correct format;
    • Excel Double Click Mouse the format painter;

format painter

  • Apply the format by clicking in all other sections many time as you want, until you unlock it, clicking the format painter once again or pressing [Esc].

#5. Auto-Fill formula down / Auto Fill Handle Copy a Pattern of Numbers or even Dates

Manual keying of data or formula is time consuming. To save time filling a column with a formula or data that follows a pattern use the Auto-fill feature.

    • In case of a formula, just fill in the first row and double click the right-side corner of that cell.

auto fill down

    • In case of a data following a pattern, like months shown below, type on first two rows then highlight the cells and double click on the right-side corner. Data can be text, numbers or dates. You can also use a shortcut [Ctrl]+[D] after you selected the cell you want to copy and cell or range below you want to paste to.

Auto fill

#6. Adjust selected Column(s) or Row(s) Width

A column or a row width can be so small such that the data is unreadable. To Auto fit the column/row, select it and click on the edge.

The same trick applies to a range of columns or rows. If you have multiple squeezed columns, there is no need to auto adjust one at a time. Select the range and click at the edge or use a shortcut [ALT]+[O][C][A].
adjast columns

#7. Jump Go to the first/Last Row/Column with Data

Save time spent on scrolling to the last or first row/column by doing the following.
Select the cell and click on the right border to move to the last column or bottom border to move to the last row. You can also use a shortcut [Ctrl]+(Arrow) to move up, down or sideways.
jump to edge

#8. Drill down into the detail, Pivot Table Data

To drill down a summary value in a pivot table, just double click it and excel will create a new worksheet and display the details. For example, to get the details for Qtr2 2015 below, double click on value.
Pivot Table Data
Then automatically generates addition worksheet shows related details records.

#9. Pivot Chart Drill Down

Double clicking on a Pivot Chart horizontal axis will activate the drill down functionality.

#10. Splitting worksheet

You can split a workbook into separate scroll-able panes for easy comparison using the Split feature in excel or simply by selecting the cell where to vertical and horizontal split and use shortcut [ALT]+[W][S]. You can also double click on grey line which you want to hide it.

#11. Maximise / minimise window

To maximise or minimise a window, double click on the top most part of the ribbon above the tabs or use a shortcut [windows]+[down]/[up].
maximise or minimise

#12. Tracing Precedent Cells

Double click in cell which there is a reference and jump to cells referenced cell. Before you have to changes default setting under the Advanced section of the Options window in Excel 2007. In Excel 2003, this option appears on the Edit tab of Excel’s Options window and is labelled Edit Directly in Cell. In either case, once you turn the option off, double-clicking a worksheet cell will highlight the precedent cells that a given formula refers to.

#13. Open Format Chart Area Options

To easily access chart formatting options, double click anywhere on the chart or click anywhere on the chart using shortcut [Ctrl]+[1].

#14. Place and Edit the Text in Shape

To place a text in a shape, double click the shape and type your text. To edit the text, double click the shape and then double click the text.

#15. Close Excel workbook

One of the favorites for some: Close a workbook quickly by double clicking the top most left part of a workbook or use shortcut [Alt]+[F4].
Close Excel

#16. Select and Edit Words in a Cell

Select the cell with the text, on the formula bar, double click the word to select it.

#17. Extra Bonus. Triple-click in MS Word

Automatically selects an entire paragraph in MS Word.

Which one is most useful/powerful Excel Double Click mouse for you? Do you know any other double click trick in Excel which I haven’t mentioned in this post? Please share your answer.

Polish Version