Tag Archives: Excel

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

Top 20 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 time, and it’s easier to use compared to other methods. Therefore, I decided to share my top 20 Excel Double Click Mouse including Excel shortcuts.

#1. Skip the OK button

While formatting the cells, you will see the window in which you can change settings and approve your changes by clicking the OK button. You can skip this activity by double-clicking the required settings.

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

By double-clicking the Format Painter, it will be locked and can be used as many times as you want until you unlock it.

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

format painter

  • You can unlock the Format Painter by 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, 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 the data in 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 a cell or a 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 border 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 generated additional worksheet shows related detailed records.

#9. Pivot Chart Drill Down

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

#10. Generate subcategories a series of data from a pivot table

 

#11. Displaying a series of data from a pivot table

 

#12. Splitting worksheet

You can split a workbook into separate scrollable 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.

#13. Maximise / minimise window

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

#14. Tracing Precedent Cells

Double click in cell which there is a reference and jump to cells referenced cell. Before you change 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.

#15. 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].

#16. 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.

#17. Close Excel workbook

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

#18. Tracking the source of a photo taken with the camera

#19. Select and Edit Words in a Cell

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

#20. 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</a href>