This How To Use Excel article is a complete guide for you if you are looking for a way to start Microsoft Excel from scratch. I have used Excel 2013 to write this article, but if you are using Excel 2007 or 2010, no worry, this article will be also useful. In this post, you will learn the following Excel topics:
- How to start a new Excel workbook
- How to fill a column with the month names using Flash Fill tool
- How to enter values into worksheet cells
- How to format worksheet cells
- How you can apply a professional look to your worksheet
- How to sum values of cells
- How you can create a chart
- How you will print your worksheet
- And finally you will learn how to save your workbook
You can browse the following links if you want to better understand this article. But not browsing these links will be also okay.
- Keyboard Shortcuts to move around Excel worksheet
- What is Ribbon in Excel
- Creating and copying formula using relative cell references
We are going to create a simple monthly revenue projection table and chart step by step in Excel to teach you how to use Excel. If you are not familiar with Microsoft Excel environment, this project will introduce you practically with Excel 2013 environment.
To follow me with this tutorial, at first download the working files from the download section. Download section is at the end of the post.
Start a new Excel Workbook
We need a fresh and empty workbook. To do this, open any of your old Excel files. To create a new, blank workbook, press CTRL + N (also you can use this command line: File➪ New➪Blank Workbook). If you don’t have any old Excel file, just right click your mouse on any folder (Desktop is a folder and my computer is not a folder, remember it) and select ‘Microsoft Excel Worksheet’ from ‘New’ options. A fresh and empty workbook will be displayed.
The monthly revenue projection table will be made up of two columns. The first column will contain the name of months, and the second column will store the projected Revenue volumes. Let’s start the journey then.
- Click A1 cell in the worksheet. A1 is located at the upper-left corner in the worksheet. You can select A1 using your mouse pointer or the navigation keys. The Name box will display A1.
- Type ‘Month’ into cell A1 and press Enter. By default, the cell pointer will move down by one cell. The cell pointer movement can be different. Click here to know more.
- Select of Move the cell pointer to B1, type ‘Projected Revenue’, and press Enter. The text extends beyond the B1 cell width, we shall fix it very soon, don’t worry.
Filling in a Column with the Month Names
We shall fill in our column A with the month names.
- Move the cell pointer to cell A2 and type ‘Jan’ from your keyboard. ‘Jan’ is the abbreviation of January. You can manually fill the cells A3, A4, A5, … …, and A13 with ‘Feb’, ‘Mar’, ‘Apr’, … …, and ‘Dec’ respectively. But we are using Excel, so we should take some advantages of this software.
- Select cell A2 clicking your mouse pointer on this cell. This is now the active cell with a heavy border around it. Notice that there is a small square box at the bottom-right corner of this active cell. This small square box is called Fill Handle. Move your Mouse Pointer over the fill handle, click the fill handle, and drag down until you’ve selected cells from A2 to A13.
- Release the mouse button, and Excel automatically will fill in the month names in abbreviated form.
Entering the Sales Data
Next, you provide the sales projection numbers in column B. Assume that January’s revenues are projected to be $100,000, and this revenue will increase by 5 percent every month.
- Select the cell B2 using mouse or keyboard whatever you prefer. Click here to know Keyboard Shortcuts for Moving around Excel Spreadsheets. Move the cell pointer to B2 and type 100000, the projected revenues for January. We shall use the dollar sign ($) and comma in our revenue numbers later in our ‘formatting number’ section.
- Now, we want to know the projected revenues for February and other months. We have fixed that in every month our revenue will increase by 5%. So, in the month of February, it will be January’ revenue+5% of January’s revenue. In equation we can show: February’s revenue= January’s revenue + 5% of January’s revenue= Value of B2+ 5% of Value of B2= B2+ 0.05*B2= B2*(1+0.05)=B2*1.05= 1.05*B2 (5%=5/100=0.05). Let’s select the cell B3 and type this formula: =B2*1.05. If you press Enter, B3 cell will display 105000.
- For the month of March the revenue will be: February’s revenue + 5% of February’s revenue= Value of B3+ 5% of Value of B3= B3+ 0.05*B3= B3*(1+0.05)=B3*1.05= 1.05*B3, (5%=5/100=0.05). We can manually put this formula for every cell but we are using Excel and Excel thinks about the best convenient way you can perform your job. We are going to copy the formula of cell B3 for cell B4. Read how to create and copy formula using relative references in Excel. Click on cell B3 to make it active. Click the B3 cell’s fill handle, drag down to cell B13, and release the mouse button.
Observe the following image. Notice that the cell B2 only holds number (100000), other cells B3, B4, … …, and B13 hold formulas and the internal formulas are generating the numbers in these cells. If you change the revenue of January month, you will see that the revenues have been changed for other months.
Formatting the numbers
Now we shall know how to format the numbers in the cells. Without formatting, it is hard to read the numbers.
- We shall select the numbers between cell B2 to B13. To do this, click cell B2 and drag down to cell B13. This time, don’t drag the fill handle, because you’re selecting cells, not filling the range.
- Choose Home⇒Number⇒Drop-down Number Format ( by default ‘General’ is selected ). Select Currency from the list. The numbers now display with a currency symbol (by default it is the symbol of a pound sterling) and two decimal points. Decimal points are not necessary for this type of big number projection.
- We shall diminish the decimal points from the numbers. Select again the cell range B2: B13. Now choose Home⇒Number⇒Decrease Decimal. Click Decrease Decimal for two times to diminish the decimals from the numbers.
Making our worksheet look a bit professional
So far we have made a functional worksheet, but it’s look is not professional. We shall work now in the appearance department. It is easy to convert this range of data to a professional and attractive Excel table. Let’s do it.
- Select any cell within the range A1: B13.
- Choose Insert➪Tables➪Table. ‘Create Table’ dialog box will appear on the screen. You will see that Excel has guessed the range properly, if not select the whole range.
- Click OK to close the ‘Create Table’ dialog box. Excel will apply its default table format and Table Tools ➪ Design contextual tab will be visible.
Our worksheet after making the cell range into a table
You can choose more Table styles from from the Table Tools ➪ Design ➪ Table Styles group. ‘Tables Tools’ contextual tab will be visible when you will select any cell within the table. When you will hover your mouse pointer over the table styles, you will get the preview. When you find appropriate one, click it and that style will be applied to your table.
Summing the values
You can easily find out the total revenue projection for the whole year, just clicking some controls. It’s simple. Let’s do it.
- Click any cell in the table.
- Choose Table Tools ➪ Design ➪ Table Style Options ➪ Total Row. Again ‘Table Tools’ is visible only when you click any cell of a table, otherwise, it is disabled. Excel automatically adds a new row to the bottom of your table, including a formula that calculates the total of the Projected Revenue column.
- You may prefer to find out more options (like Average, Max, Min and others) rather than ‘Total’ projection of Revenues. Click B14 cell and choose your preferred one from the drop-down list.
Creating a chart
We shall create a chart to know revenue growth visually.
- Activate any cell in the table.
- Choose Insert ➪ Charts ➪ Recommended Charts. Excel will display the ”Insert Chart’ dialog box with suggestions.
- In the ‘Insert Chart’ dialog box, click the second recommended chart (a clustered column chart), and click OK. Excel will insert this chart in the center of the window. To move the chart to another location, click its border and drag it.
- Click the chart and choose a style using the Chart Tools ➪ Design ➪ Chart Styles options.
The following image shows the chart we have created in our worksheet. Your chart may differ with our one if you have selected different Chart Styles.
Printing your worksheet
It is easy to print your worksheet if you have a printer attached to your working station and it works properly.
- When you are printing your worksheet, make sure that the chart isn’t selected. If the chart is selected, the chart will print on a page by itself. Just press Esc or click any cell to deselect the chart.
- Click the Page Layout button on the right side of the status bar in the Excel worksheet. You will see the Page Layout View of your worksheet, page-by-page. You can easily see how your printed output will look. The following figure shows the worksheet zoomed out to show a complete page. In Page Layout view, you can decide immediately whether the chart is too wide to fit on one page. If the chart is too wide, click and drag a corner of the chart to resize it or just move the chart below the table of the number.
- If you’re ready to print, choose File ➪ Print. You can change some print settings at this point. For example, you may choose to print in landscape rather than portrait orientation. If you make the change, and you will see the result in the preview window.
- When you’re satisfied with your settings, click the Print button in the upper-left corner. The page is printed, and you’re returned to your workbook.
Saving your workbook
Until now, everything that we’ve done has occurred in our computer’s memory. Your power may fail, all may be lost- unless Excel’s Auto-recover feature happened to start in. So it’s always better to save your work to a file on your hard drive.
- Click the Save button on the Quick Access toolbar. Save button in the Quick Access Toolbar looks like a floppy disk. The workbook hasn’t been saved yet, so it has still its default name. Excel will respond with a Backstage screen that will ask you to choose where you want to locate the workbook file. The Backstage screen lets you save the file to an online location or to your local computer.
- After selecting Computer, click Browse. Excel will display the Save As dialog box. You can also double-click Computer to open the Save As dialog box.
- Enter a name (such as Table-and-chart) in the File Name field. Now click Save or press Enter. Excel will save the workbook as a file. The workbook will remain open so that you can work with the file.
By default, Excel saves a backup copy of your work automatically every ten minutes. To adjust the Auto-recover setting (or to turn if off), choose File ➪ Options, and click the Save tab of the Excel Options dialog box. However, you should never rely on Excel’s Auto-recover feature. So I advise you to save your workbook frequently.
Additional Resources on How-to-use Excel
You must not stop your journey after creating your first Excel workbook. You will proceed your learning on Excel. Here are some links that will help you to advance your journey of learning Excel.
- Learn Excel Charting – You must learn how to create graph or chart in Excel. Here is the complete video guide on creating chart on Excel.
- Excel Conditional Formatting – Learn how to format cells conditionally using Excel’s conditional formatting rules. Learn Excel Conditional Formatting completely with these video tutorials [14 videos & 17 practice problems].
- Excel Pivot Table – If you are not expert in Excel Pivot Table, then never demand yourself as an Excel Pro user. Here are the 23 steps to becoming an Excel Pivot Table Wizard.
- Data Analysis – Here is 20 data cleanup techniques in Excel.
- Excel Functions and Formulas – Here you will get some important Excel Functions.
- Excel Solver – Learn how to work with Excel Solver. A complete step by step guide to learn Excel Solver.
- What-If-Analysis – You will create a dynamic business model using What-If Analysis in Excel. A step by step complete guide to learning What-If Analysis in Excel.
- Excel VBA and Macros – If you think you are matured enough to learn Excel VBA. Here is the complete step by step guide to learn Excel VBA from scratch.
How-to-use Excel Resources on the Web
- Peltier Tech Blog – If you have thought to learn better Excel Charting, there is no Guru other than Jon Peltier.
- Mr. Excel Forum – Mr. Excel and Bill Jelen is on the web from the year 1998. The site and the forum have huge resources on Excel.
- Excel Guru – Ken Puls has created this great site. You will get huge Excel, Access, Outlook, and PowerPoint related resources… I don’t want to say more, just visit the site, what not there!
- Chandoo – A giant site emphasizing on Excel Charting, Dashboard, Project Management Templates and so on. Chandoo provides regular tips and tricks on Excel.
I have just finished the list with four only as I know you have just started with Excel. To get a comprehensive list of Excel resources, visit Rick Grantham’s Best Excel Resources and Sites.
If you’ve followed me to create the above workbook, you may have realized that creating a workbook was not difficult. But, of course, you’ve not scratched the details of Excel. Slowly, we shall cover more details of Excel. So, this was a brief description on How to use Excel.
Happy Excelling 🙂
Download Working File
Download the working file from the link below: