Your First Steps in Excel – Beginner’s Crash Tutorial

This step-by-step tutorial will give you a quick overview of Excel with examples of how to use basic calculations, formulas, and Pivot Tables. Download a sample Excel file for practical examples and scroll to the bottom of the page to claim your free One-to-One Online Course.

Step 1: Your First Excel Workbook

Every Excel workbook is a collection of worksheets. Think of an Excel worksheet as a page and a workbook as a folder that binds those pages together. Also, every sheet consists of rows and columns. A row is marked with a number (e.g. 2, 5, 123) and columns are marked with a letter (e.g. D, G, I).

A cell is an intersection of a row and a column. Each cell in Excel has a unique address. For instance, a cell located at the intersection of Column A and Row 3 has an address “A3” (with the letter referring to the spreadsheet column and the number to the Excel row). 

Step 2: Entering Data into a Spreadsheet

To create your first spreadsheet, open a new Excel workbook. Then, click on the cell B2 and type in a word “Product” followed by hitting the Enter key. Finally, click on the cell to the right (cell C2) and type in “Items” and then “Price” in the next cell in your spreadsheet (C3). As a result, you will see a header of a three-column Excel table which we will fill in with values in the next step.

Now it’s time to fill the values corresponding to each header and complete your first Excel data table. Download the sample Excel file for your reference. 

Step 3: Excel Data Tables

Excel spreadsheets allow you to use two inputs – number or text. We will use three columns, each with its header. Firstly, we want to fill each column with appropriate values. Type in “Coffee” in the first cell below the header “Product”, then “Water”, then “Tea” and repeat that step a few more times (the order of the products do not matter). Also, fill the rows beneath the “Items” columns with random numbers (i.e. 2, 5, 8, 6, etc.). Similarly, fill the “Price” column with random prices (i.e. 2.5, 3.2, 2.8, etc.).

Try creating an Excel data table on your own. Download the sample Excel file for an example and compare it with your workbook. Congratulations, you have just created your first spreadsheet!

Step 4: Basic Calculations in Excel

Let’s now create another column and introduce a first simple calculation. Although Excel has hundreds of advanced formulas, you will discover that most of the time you just need basic arithmetic calculations like additions or multiplications.

First, create a column “Subtotal”, which will be an output of multiplying a value from column “Items” by “Price”. To do that, click on the cell E3 and type in “=” followed by “C3”, then “*” (Shift+8, * stands for the sign of multiplication) and “D3”. As a result, your formula in the cell E3 should say “=C3*D3”. When you hit Enter, you should see a number that equals the number of products x the price. Note: If you want to divide numbers use “/”, additions are represented by “+” and substractions by “-”.

Step 5: Relative Referencing

Let’s replicate the same formulas in the cells below. The good news that you won’ have to do it manually thanks to Excel’s ability to dynamically update cell references.

To fill the rest of the column, stay on the cell E3 and left-click on the lower right corner. Then, drag the cell to the bottom of the table and release the mouse. Finally, double-click on one of the cells to inspect the formula – you will see that the cell references updated themselves to the current row (i.e. C3 * D3 became C5 * D5, C8 * D8, C12 * D12, etc.). That is what in Excel is called Relative Referencing.

Step 6: Excel SUM Function

Now let’s calculate the totals for each of the columns. First, go to the last row of the table and underneath the last Product value type in TOTAL. Use SUM formula to compute the total of values from a range of cell. Then, move one cell to the right to the column C and type in “=SUM(“. Finally, left-click on the cell with the first value in the column (cell C3) and then drag it to the last value.

You will see that the formula shows “=SUM(C3:C15”. The “:” indicates that we are selecting a range of values between the cell reference to left and the right of the colon. Close the bracket “)” and hit Enter. You can repeat the same step for the columns “Subtotal” and “Total”. You can also copy (Ctrl+c) the Excel formula you have just created and paste (Ctrl+v) it underneath the remaining columns.

Step 7: Absolute Referencing

Let’s create another column and call it “VAT”. Here we will multiply the values in column “Subtotal” by a fixed percentage value, e.g. 20%. To do that, click on the cell J2 and type in “VAT”. Then type in 20% in the adjacent cell K2. Finally, go back to the first cell in the VAT Column (cell F2) and type in a formula “=E3 * $K$2”. Notice the dollar signs before the letter K and the number 2.

If you drag the cell and copy the formulas to the last row of the table, you will notice that the first part of the formula (E3) changes its reference to the current row. However, the $K$2 always stay the same. The dollar signs are responsible for fixing or anchoring that part of the formula to a particular cell. This is what in Excel is called an absolute referencing.

Finally, create a Total column by adding Subtotal and VAT (i.e. “=E3 + F3”). If you now change the VAT% value in the cell K2, the subtotal and total values will update accordingly.

Step 8: Formatting in Excel

It is important to present your Excel output in a transparent and easy to follow way. Keep the numbers apart by changing the column widths, and use different font styles to visually keep the numbers apart. Also, use borders to visually separate the totals from rest of the table. Likewise, fill the headers with a distinct colour.

To display your with a currency symbol, right-click on a selection of values. Then, click on ‘Format Cells’ and select the currency. You will find the most often used formatting tools in the left section of the Home tab of your Excel workbook.

Step 9: How to use a V-lookup Formula

V-lookup function allows you to take an input from your existing Excel spreadsheet, reference it with another data source, and return value that is related to that input. To see how it works in real life, download the sample Excel file and click on the ‘Extended Data’ sheet. You will notice another column called VAT% (i.e. a sales tax), which has a different vat or tax value for each product (e.g. Coffee 16.0%, Tea 12.0%, etc.).

To display the appropriate VAT or Sales Tax value, the v-lookup formula takes the look-up value (the first argument), for instance, the product name. Then, it tries to find it in the first column of the reference table (the second argument). In our example, the reference table is located in the ‘VAT Lookup’ sheet. The third argument, col index, describes the position of a column in the reference table from which the value (i.e. the relevant VAT%) will return. Leave the fourth argument (approximate match) as FALSE.

Step 10: How to create a Pivot Table in Excel

A pivot table is a powerful tool that will help you to present and summarise your data. To create a pivot table, select the entire sheet or data table (click on any value and hold Ctrl + A on your keyboard) but make sure to exclude the ‘Total’ row. Then, go to to ‘Insert’ (or ‘Data’ in some Excel versions) tab and select PivotTable. Finally, choose an option to add the pivot table on a new Excel worksheet.

You will see a blank table on the left side of the newly created sheet. Also, to the right, there will be a list of fields with a grid of four rectangles beneath it. Drag the ‘Product’ field to the ‘rows’ rectangle and ‘Total’ to the values. You will now see the summary of your sales with the subtotal for each product group. You can also drag ‘Product’ field to filters and select only one of the values. Compare your results with the pivot table in the sample Excel file.

Excel Beginner’s Tutorial – Summary:

To learn Excel, start with a blank page and create a simple table with just three columns. Fill the first column with names (for instance, products), the second with number of items sold, and the third with corresponding prices. To try basic Excel calculations, add a ‘Subtotal’ column and insert a formula that multiplies units number of items by its price. Copy and paste the Excel formulas to the bottom of the table. You can now add the values of each column together by using the Excel SUM formulas.

For more advanced tasks, add a VAT column. Fill this column with a formula that will take the subtotal and multiply it by a fixed VAT% value. This value will be located in one cell only, so you need to use absolute referencing. In other words, you need to enclose the cell reference in dollar ($) signs.

Try using V-lookup formulas to apply different VAT values to different product names. Summarise the data with a pivot table. Place product in rows or filter, and drag the Totals into the ‘values’ rectangle. Download the sample Excel file to compare your results with this tutorial’s workbook.

 

Hi, my name is Jacek and I love Excel and data. Hope you’ve enjoyed reading this tutorial as much as I enjoyed writing it! If you have any questions about Excel or data analysis in general or any topic in particular, don’t hesitate to get in touch.

If you would like to book my One-to-One Excel Crash Course, click here and claim your first Lesson for free! For more info about my Data Analysis and Financial Modelling Consulting services, visit this page.

 

Related Posts:

Learn How to Become a Self-Taught Data Analyst – In this blog, I’m sharing some tips and resources I have found useful on my journey to become a data analyst.

How to Analyse Data in Excel with Power Query and a Pivot Table – Here you can find an example of using more advanced functions of Excel to analyse data.

How to Use Cohort Analysis to Calculate Retention and Churn Rate in Excel – This advanced Excel tutorial will show you how you can apply spreadsheets and pivot tables to analyse users’ behaviour.

How to Build a Cash Flow Forecast in Excel – This step-by-step blog will introduce you building financial models in Excel and using spreadsheets to forecast revenues and expenses.

How to Use Python and Pandas for Data Consolidation and Transformation – This blog will show you an example of using Python scripts to combine multiple csv or Excel files.