In this tutorial, we learn how to create our first spreadsheet in excel. We want to fill in the columns of data for each month of the year, but before that, we need an extra row after the title, for the column headings. To add a row to theworksheet see the instruction below:
1. Select the row (click the row number) above where you want to insert another row, e.g. select row 2. (See Image Below)
2. Click the Home tab and then, in the Cells group, click the arrow below Insert, and click Insert Sheet Rows. (See Image Below)
3. Click cell B2 in the new row, and type “January”, then press Enter twice, to move to B4.
4. Type 3950 in cell B4, press Enter, type 775 in cell B5, and press Enter again.
5. In cell B6 type = then click in B4 and type +. Click B5 (to get =B4+B5) then press Enter to see the total appear in B6. (See Image Below)
Also, Take a look at the image below.
To insert multiple rows, select a block of as many rows as you need, and then click Insert – the new rows will be inserted above the selection. Use a similar procedure to insert one or more new columns.
6. Click cell B8, and then type the values 2250, 425, 1150, 350, and 450 (pressing the down-arrow or Enter after each). (See Image Below)
7. In cell B13, type =SUM( and then click B8, type a period, click B12, type ) and press Enter, then click B13 to see the Formula Bar contents
Some of the labels in column A appear truncated. The full label is still recorded, but the part that overlapped column B cannot be displayed, if the adjacent cell is occupied.
To change the column width to fit the contents:
1.Select the column of labels (click the letter heading). (See Image Below)
2. On the Home tab, in the Cells group, select Format.
3. Under Cell Size, select AutoFit Column Width.
4. Alternatively, move the mouse pointer over the column boundary, and drag to manually widen or double-click to AutoFit to contents. (See Image Below)
To change a group of columns, select the first, hold down Shift and select the last. For non-adjacent columns, select the first, hold down Ctrl and click other columns.
We Take it simple and will start by creating a simple, personal budget workbook, to illustrate the processes involved in creating and updating your Excel spreadsheet.
1. When Excel opens, it offers a list of recent workbooks that you used and allows you to open other workbooks, or you can select the new blank workbook which is named “Book1” by default – this can be used as the starting point for your new workbook.
2. Type the spreadsheet title “My Personal Budget” ( or something else you want) in cell A1, and press the down arrow Key, or the Enter key, to go to cell A2 (or just click cell A2 to select it)
⇒ The Excel Start screen displays templates, and lists recent workbooks. You could select a predefined workbook template from those stored on your computer, or online at the Microsoft website (Try it from your side).
⇒Note: Text is automatically aligned to the left of the cell, numbers are aligned to the right.
We start with a simple workbook, to show what’s involved in entering, modifying, and formatting data, and in performing calculations. This includes ways in which Excel helps to minimize the effort. We cover printing, look at Excel Help, and discuss the various file formats associated with Excel. So Let’ Start.
When you launch Excel, you usually start with the Excel window displaying a blank workbook called “Book1”:
Each workbook opens in its own window, making it easier to switch between workbooks when you have several open at the same time.
1.Move the mouse over a command icon in one of the groups (e.g. in Alignment, on the Home tab) to see the command description.
2. Click the down-arrow next to a command (e.g. Merge & Center) to show the list of related commands. (See Image Below)
3. Click the arrow by the group name (e.g. Alignment) to see the associated dialog box. (See Image Below)
4.Select other tabs to view other cell formatting options.
The Home tab contains all the commands for basic worksheet activities, in the Clipboard, Font, Alignment, Number, Styles, Cells, and Editing groups.
By default, Excel provides one array of data (called a worksheet) in the workbook. This is named “Sheet1”. Click the + button to add “Sheet2”, “Sheet3”, etc. (See Image Below)
These are the theoretical limits for worksheets. For very large numbers of records, a database program may be a more suitable choice.
Each worksheet is the equivalent of a full spreadsheet and has the potential for up to 1,048,576 x 16,384 cells, arranged in rows and columns. The rows are numbered 1, 2, 3 and onward, up to a maximum of 1,048,576. The columns are lettered A to Z, AA to ZZ, and then AAA to XFD. This gives a maximum of 16,384 columns. The combination gives a unique reference for each cell, from A1 right up to XFD1048576. Only a very few of these cells will be visible at any one time, but any part of the worksheet can be displayed on the screen, which acts as a rectangular “porthole” onto the whole worksheet.
The actual number of cells shown depends on screen resolution, cell size, and display mode (e.g. with Ribbon minimized or full-screen). Use the scroll bars to re-position the screen view, or type a cell reference into the name box, e.g. ZN255. (See Image Below)
One worksheet is usually all you need to create a spreadsheet, but it can sometimes be convenient to organize the data into several worksheets. See here for other ways to navigate through the worksheet using arrow keys, scroll functions, and split views.
Chances are good that Excel 2019, as it comes when first installed, is not always the best fit for the way you use the program. For that reason, Excel 2019 offers an amazing variety of ways to customize and configure the program’s settings so that they better suit your needs and the way you like to work.
This Tutorial covers the most important methods for customizing Excel settings and features. The tutorial looks at three basic areas where you can tailor the program to your individual needs:
⇒ The first place ripe for customization is the Quick Access toolbar. Not only can you control which Excel command buttons (on and off of the Ribbon) appear on this toolbar, but you can also assign macros you create to this toolbar, making them instantly accessible.
⇒ The second place where you may want to make extensive modifications is to the default settings (also referred to as options) that control any number of program assumptions and basic behaviors.
⇒ The third place where you can customize Excel is in the world of add-ins, those small, specialized utilities (sometimes called applets) that extend the built-in Excel features by attaching themselves to the main Excel program. Excel Add-ins provide a wide variety of functions and are available from a wide variety of sources, including the original Excel 2019 program, the Microsoft Office website, and various and sundry third-party vendors.
Using Excel’s Own Add-ins
Office Add-ins aren’t the only ones that you can use to extend Excel’s features. You can also use built-in add-ins created by Microsoft or third-party Excel Add-ins that you can purchase from a wide variety of vendors. Before you can use any Excel Add-in program, the add-in must be installed in the proper folder on your hard drive, and then you must click the add-in in the Add-Ins dialog box. (See Image Below)
There are two different types of Excel Add-in programs immediately available that you can use to extend the features in Excel 2019:
Excel Add-ins: This group of add-ins (also known as automation add-ins) is designed to extend the data analysis capabilities of Excel. These include Analysis ToolPak, Euro Currency Tools, and Solver.
COM Add-ins: COM (Component Object Model) add-ins are designed to extend Excel’s capability to deal with and analyze large amounts of data in data models (collections of related database tables). These include Inquire, Microsoft Office Power Pivot for Excel, and Power View.
When you first install Excel 2019, the add-in programs included with Excel are not loaded and therefore are not yet ready to use. To load any or all of these add-in programs, you follow these steps:
1.Click the File menu button, click Excel Options or press Alt+FT to open the Excel Options dialog box, and then click the Add-Ins tab.
The Add-Ins tab lists all the names, locations, and types of the add-ins to which you have access.
2. (Optional) In the Manage drop-down list box at the bottom, Excel Add-Ins is selected by default. If you want to activate one or more of your COM add-ins, click COM Add-Ins from the Manage drop down list.
3. Click the Go button.
If Excel 2019 Add-Ins was selected in the Manage drop-down list box, Excel opens the Add-Ins dialog box (similar to the one shown in Figure below), showing all the names of the built-in add-in programs you can load. If COM Add-Ins was clicked, the COM Add-Ins dialog box appears instead. (See Image Below)
4. Click the check boxes for each add-in program that you want to be loaded in the Add-Ins or COM Add-Ins dialog box.
Click the name of the add-in in the Add-Ins Available list box to display a brief description of its function at the bottom of this dialog box.
5. Click the OK button to close the Add-Ins or COM Add-Ins dialog box.
Excel Add-in programs are saved in a special file format identified with the.XLL or .XLAM (for Excel Add-in) filename extension. These files are normally saved inside the Library folder (sometimes in their own sub-folders) that is located in the Office 16 folder. The Office 16 folder, in turn, is located in your Microsoft Office folder inside the Program Files folder on your hard drive (often designated as the C:\ drive).In other words, the path is c:\Program Files\Microsoft Office\Office16\Library After an add-in program has been installed in the Library folder, its name then appears in the list box of the Add-Ins dialog box.
Excel 2019 relies primarily on the onscreen element called the Ribbon, which is the means by which you select the vast majority of Excel commands. In addition, Excel 2019 sports a single toolbar called the Quick Access toolbar, some context-sensitive buttons and command bars in the form of the Quick Analysis tool and mini-bar, along with a number of task panes such as Clipboard, Research, Thesaurus, and Selection to name a few.
Among the features supported when selecting a certain style and formatting commands are the Live Preview, which shows you how your actual worksheet data will appear in a particular font, table formatting, and so on before you actually apply it. Excel also supports an honest-to-goodness Page Layout view that displays rulers and margins along with headers and footers for every worksheet. Page Layout view has a zoom slider at the bottom of the screen that enables you to zoom in and out on the spreadsheet data instantly. The Backstage view attached to the File tab on the Excel Ribbon enables you to get at-a-glance information about your spreadsheet files as well as save, share, preview, and print them. Last but not least, Excel 2019 is full of pop-up galleries that make spreadsheet formatting and charting a real breeze, especially with the program’s Live Preview.
The following table lists some of the actions that you may want to carry out and indicates the Ribbon tabs and groups where the associated commands for these actions may be found in Excel 2019:
Excel 2019 Sleek Look and Feel
If you’re coming to Excel 2019 from Excel 2007 or Excel 2010, the first thing you notice about the Excel 2019 user interface is its comparatively flat and decidedly less colorful display. Gone entirely are the contoured command buttons and color-filled Ribbon and pull-down menu graphics along with any hint of the gradients and shading so prevalent in the earlier versions. The Excel 2019 screen is so stark that even its worksheet column and row borders lack any color, and the shading is reserved for only the columns and rows that are currently selected in the worksheet itself.
The look and feel for Excel 2019 (indeed, all the Office 2019 apps) is all part of the Windows 10 user experience. This latest version of the Windows operating system was developed primarily to work across a wide variety of devices from desktop and laptop to tablets and smartphones, devices with much smaller screen sizes and where touch often is the means of selecting and manipulating screen objects. With an eye toward making this touch experience as satisfying as possible on all these devices, Microsoft redesigned the interface of both its new operating system and Office 2019 application programs: It attempted to reduce the graphical complexity of many screen elements as well as make them as responsive as possible on touchscreen devices.
The result is a snappy Excel 2019, regardless of what kind of hardware you run it on. And the new, somewhat plainer and definitely flatter look, while adding to Excel 2019’s robustness on any device, takes nothing away from the program’s functionality.
Sign up for new Howexcel Latest Post & Training,
Subscribe To Our Newsletter
Join our mailing list to receive the latest Tutorials From Howexcel.