• Got it Pass Team

MS Excel for Beginners: Introduction to Excel 2019

Updated: Sep 30, 2020

For those who do not already use MS Excel, it is probably the most valuable and powerful tool used in the accountancy world.


Think of each workbook (Excel file) as a huge notepad of 256 pages, with each sheet split into 256 columns and over 1 million rows, making up huge numbers of ‘pigeon holes’ (cells) to take your data.


However, unlike a paper notepad, this is all held on your PC electronically, and allows you to change data, insert or delete rows and columns, use formulas which can recalculate as soon as a number is changed, and cross reference to other sheets within the workbook, or even completely separate workbooks.


You can insert graphs and charts, change colours of text and numbers, and make borders around data areas to make them stand out.


In short once you have data captured into a workbook there are very few difficulties in turning your data into an easy to read report or dashboard using Excel.



What is new in Excel 2019?


The Microsoft Corporation is continuously improving Excel, and the latest version is Excel 2019.


One of the most useful improvements is six new functions. The big advantage of these new functions is that they allow many pairs of conditions to be added without using nested IF functions. Nested IF functions have always been a problem area in Excel, in that they are very difficult to read and debug if there many levels within the formula. If you are trying to understand a nested IF that a colleague has put together, you may find it difficult if it has a number of levels.


The new functions use a different approach and are easier to follow what exactly is being achieved by the formula.


The new functions that allow many conditions are IFS, SWITCH, TEXTJOIN, CONCAT, MAXIFS and MINIFS.



Also, on the Data tab on the ribbon, the user can connect to many more data sources such as cloud-based data or data in a table on a web page. You could even connect to your accounting system if it supports ODBC (Open Database Connectivity)




Graphics are also improved, with stock 3D images available now, and SVG (Scalable Vector Graphics) support, which means that the image will not turn into pixels when you increase the size of it.


You can also share your workbook with colleagues if it is on Sharepoint or OneDrive. Up till now, only one user could edit the workbook. If anyone else opened it they had read only access until the current user had closed the workbook, which was particularly annoying if the current user had left the workbook open and then gone into a meeting!


Cell Styles and Formatting


Cell styles and formatting can be used to give your spreadsheet a professional look and make things stand out.


When you have entered a number or text into a cell, it looks rather plain. Also, if you have entered a SUM formula, you may wish to make this stand out more from.


If you click on Cell Styles in the Style section of the Home tab on the ribbon, you will see a pop-up menu for different styles which can be applied to a single cell or a range of cells.


Cell Styles in the Style section of the Home tab on the ribbon

Pop-up after clicking on 'New Cell Style'

By hovering your cursor on each option, you will see how it will show in your selected cell or selected range.



These are the default styles that come with Excel, but you can also create a custom style by clicking on ‘New Cell Style’ at the bottom of the pop-up



This will produce a further pop-up which will allow you to name and customise your style. A default name will be given, but you can use a name of your choice.



Clicking on the Format button (in the pop-up) will allow you to select the formatting for your particular style.


This menu has tabs across the top that allow you to change the number format, alignment in terms of how you want see your value within the cell e.g. if text is long, allowing it to wrap within the cell or merge to make it spread across more than one cell, change font typeface, size and colour, put a border around the cell or a block of cells, or fill the cell background with a selected colour.


The protection tab only works if you have protected the worksheet with a password. You can select which cells you want to be protected so that other users cannot change them.


Your new style will appear in a Custom section at the top of the Styles pop-up.


Good cell formatting can make your spreadsheet look really professional and readable rather than just a jumble of numbers and text.


If you want to directly format cells without using styles, you can do this by right clicking on the selected cell and then click on ‘Format Cells’ from the pop-up menu. This will give you access to the same pop-up menu as shown above.



Tables


Excel tables are useful for presenting tabular data, and allowing the user to filter on it.


For example, you could have range of tabular data as follows:


Example on Excel Tables

You can turn this into a table by clicking on ‘Format as Table’ in the Styles section of the Home tab on the ribbon. This will provide a pop-up allowing you to select a style for your table.


You can create your own table style by clicking on ‘New Table Style’ which will show a pop-up allowing formatting of the various components of the table.


Once you select a style, a pop-up will request the range for the table. The table data usually has header columns, so make sure that the headers box is ticked.


Clicking on ‘Format as Table’ in the Styles section of the Home tab on the ribbon

Pop-up requests the range for the table

The table of data will now look like this (depending on the style selected)


The table has data for quantity and price, but not for the product of quantity and price.


To add this all we need to do is to put a formula into cell D2 (+[@Quantity]*[@Price]).


This will automatically add a new column into the table with the product formula copied all the way down


You can add in column totals by selecting the column cell in row 6 and clicking on AutoSum (under Editing in the Home tab of the ribbon)


The drop down will give you options to change the function e.g. set to Average


You can use the filter drop downs on the column headers to select specific criteria so as to show a subset of the data. This has a huge advantage over normal data filtering in that the totals do not disappear, but are recalculated according to the filter selection.




Conditional Formatting


This function allows you to format cells so that the format changes according to the value shown. For example, if a Profit and Loss Report shows a loss, then the background of the cell could be changed to red, whereas if a profit was made the background would change back to normal.


Select the cell or cells that you want the rule to apply to.


Conditional Formatting is found in the Styles section of the Home tab on the ribbon.


For a simple example, click on ‘New Rule’ in the pop-up


This will produce a new rule screen


Click on ‘Format only cells that contain’, and the bottom part of the new rule screen will change.


Set the second drop down to ‘Less than’ and the value to 0


Click on the Format button and choose the colour as red. Click on OK

New Formatting Rule screen

Now if you put a negative value into your selected cell, then the background colour will turn red.


Change the value to positive and the red background will disappear


You can edit or add more rules by clicking on ‘Manage Rules’


Data Validation


You can use this to specify a rule for what data can be entered into a cell. It is an option that is hard to find on the ribbon – see where the arrow is pointing in ‘Data Tools’ on the ribbon.


The ‘Allow’ drop down gives several options, but for this example we will use ‘Whole Number’ and ‘Between’ with minimum set to 10 and maximum set to 20


You can enter an input message to prompt a user who goes to enter a value into the cell, and you can also create an error message if the rule is breached.


These two tabs are optional – if the user breaks the rule, a default message will appear.


Click on OK and that validation rule will now apply to the selected cell


Click on the cell and the input message will appear. Try entering a value outside of 10 to 20 and the error message will appear.


Note that if you enter a decimal number, e.g 15.5, this will still break the validation rule because ‘Whole Number’ was selected.





Got it Pass Team is happy to work with Richard in presenting this article.



If you find this article is helpful and you want to help others too, just share it in any social media (such as Facebook, LinkedIn).



About Richard -

Richard is an expert in MS Excel and Access with extensive experience of developing MI reporting systems, databases and dashboards using VBA or RAD for major companies such as Santander, Nationwide Building Society and Zurich Insurance. He is also the author of Access 2010 VBA Macro Programming and Excel VBA Macro Programming. He was graduated in University of the Thames Valley, United Kingdom.



Note: Some of the links in this page contain affiliate links. I will earn a small commission for any purchases you make, though there is no extra cost to you. 

The products or services I recommend only I use them myself or after my investigation and truly believe they are useful to you. Don’t forget there are many other options out there – these are simply my preferred choices.

96 views

Phone Number: +852 9667 4853

©2020 by Got it Pass.