Skip to main content

Microsoft Excel Basics

Wow, look at all those boxes. What’s a formula? How do I make a chart? I haven’t used Excel before, but I need to know things! This Excel beginner workshop will guide you to the solutions. You’ll leave here with the knowledge of the features and functiona

Introduction

Starting out:

Opening an Excel workbook takes you to the Backstage section. Here, you can open recently saved documents, create a new document, or use a template and work from there. Clicking on the search bar under the New tab allows you to make a search for any template you may need.

Once you open or create a document to work on, you're automatically taken to the Home ribbon. The tabs near the top of the document are called ribbons and each holds specific tools to help you edit and format your document.

The Quick Access Toolbar is at the top left corner. From there you can save, undo, and redo. With the dropdown bar you can customize the section to add or remove buttons.

If you ever get lost or don't know how to find a certain tool, use the search bar at the top. It may also have "Tell me what you want to do..." listed in the search bar's place depending on what version of Excel you are using. It can find tools to use directly from the search bar.

Need help understanding sheets?

Each box inside of a worksheet is called a cell. Cells are at the intersections of rows (numbered areas) and columns (lettered areas). They are given names based on their intersection. So the first cell is A1. Each cell is able to contain information, including formulas.

To move around, you can use the arrow keys to move from the cell you have currently selected. Enter and Tab also work!

When editing a cell you can type directly over it, double-click to go into the cell, or go into the Formula Bar.

The Fill Handle can be selected and dragged across the desired cells to quickly paste generated data related to the cell you've selected. This works well with numbers, months, dates. etc.

The best way to know when you have the handle ready is by hovering over the bottom right corner of the selected cell. The cursor will change from a thick, white crosshair into a thin, black crosshair.

Because ribbons are very important, the next section will go over a few of them and what features you may find within them.

Ribbons

Home Ribbon

The Home ribbon is located between File (takes you to Backstage view) and Insert. From here, we will go over a few of the features you can find within this ribbon.

Fonts & Text

By default, Excel uses Calibri font at size 11. You're able to edit your font and colors inside of the Font tool. This allows you to format content inside of a cell regarding the text. That can be copied and pasted to keep everything consistent.

Anything copied will be placed in the Clipboard. Also, using the Format Painter only copies and pastes formatting done within your copied selection.
 

Alignment & Spacing

The Alignment tools allow text in the document to change its alignment from left, center, or right. There is also an option that aligns text at the top, middle, or bottom of a cell.

In this group, you can also adjust the orientation such as diagonal or vertical text alignment. Indentation to the left or right is also available.

When typing in a cell and there's content already in the space next to it, it can be hidden from view. Wrap Text allows you to show text inside the cell as it fits the space around it and creates additional lines, making it easier to read.

Merge & Center combines selected cells inside your sheet and aligns text to the middle. It works with labeling over cells that span multiple columns or rows. The dropdown arrow for this feature gives you more options to work with and can also unmerge cells.

The Number group gives you formatting options on how you want cells to interact regarding numbers with dates, currency, percentages, etc.

 

Backgrounds & Borders

There are many ways to accent your sheet by using borders and fill colors.

Borders add thicker lines around cells. This helps with grouping information or simply making content stand out. Clicking the dropdown will give you many more choices on how you want to edit your borders. You are given the option to draw the area for borders as well.

The icon with a paint bucket, the fill color tool, has a default color you may simply click on to fill the selected cell with that color. If you want to change the color, click on the dropdown arrow and a list of colors will be available to you.

 

Conditional Formatting

This tool will offer many choices. Clicking on the Conditional Formatting button brings up these options:

Highlight Cells - based on the rules you set, cells will become highlighted once they meet the rule requirements.

Top/Bottom - offers options for top or bottom averages, percentages, etc.

Data Bars - colored bars based on selected cells' values.

Color Scales - cells given a range of colors based on data.

Icon Sets - sets of various images and shapes can be assigned to cells.

 

Insert, Delete, & Format

The last group we'll cover in the Home ribbon is simply called Cells. The tools inside this group are for editing rows, columns, or individual cells within your sheet.

When you need to include a new cell, or groups of them, click on the Insert button. It will open a dropdown with a few options: Insert Cells, Insert Sheet Rows, Insert Sheet Columns, and Insert Sheet (tabs at the bottom of the worksheet).

Delete offers the opposite. If you need to remove groups of cells, a single cell, or an entire sheet this is the correct tool.

The final button in this group is Format. This will give selected cells a specific task. These options range from hiding/unhiding, column or row size, lock cells, and more.

Pictures

To add an image, select Pictures. You will be able to open your image from a file and include it to your document. Online Pictures pulls up an image search and OneDrive browser if you do not have an image saved to your device.

When an image is in a worksheet, it creates a new format ribbon. This lets you make unique edits to the pictures, add effects, change how text interacts with it, and many other features.

 

Shapes

Shapes can be added into your spreadsheet and they range from circles and squares to arrows, stars and more.

Another nice feature is SmartArt. With this, you can make a change and the image will immediately adjust in real time. This is perfect for lists, cycles, and organization charts.

 

Charts

Columns, lines, pies, bars, and a combination are some of the options. The Insert ribbon has a section for placing Charts into your spreadsheet.

There is a wide variety of different charts you can use for your needs. It even has a Recommended Charts option. When a type of chart is selected, a dropdown appears with additional options. For example, clicking on column charts will let you preview the types of column charts: clustered, stacked, and 3D variations. Similar options are available for other chart versions as well. This group includes Maps and Pivot Charts too.

Similar options for this, but limited to a single cell, is the Sparklines group. Select the cells with your data and it will create a Line, Column, or Win/Loss representation of your information.

Tables

Creating a Table can help you stay organized. Select your desired cells and click on the Table button. This will bring up a new Design ribbon to continue editing your table.

If you have large amounts of data, or if it's rather complex, Pivot Tables are the way to go to sort content. When you already have a table in your spreadsheet, clicking Pivot Table will open a dialog box for you to select your necessary options.

Are you working with one table? Are you using an external data source? You can also choose where to put your new Pivot Table: into a new worksheet or an existing worksheet.

If you're not sure what type of Pivot Table you need to use, you can also check for recommended ones.

Page Setup

When you need to edit dimensions, size of cells, or an overall look, the Page Layout ribbon is where you need to be. The Page Setup group covers these specific functions:

  • Margins change the size between the text and page edges.
  • Change the Orientation of the page to portrait or landscape.
  • Size adjusts the entire length and width of the document.
  • Selected areas can be used for a Print Area and only prints that specific selection.
  • Page Breaks can separate sections when you want to print on new pages.
  • You're able to include a Background in your worksheet.

 

Themes

Themes can change formatting for specific colors, fonts, and effects to edit your worksheet. The dropdown shows many different options to choose from.

If you want to continue customizing your sheet, beside the Themes button are Colors, Fonts, and Effects. Each contain additional dropdowns and allows more custom features to be included if you don't see something already available.

 

Sizing & Display

You can change the height, width, and scale inside the Scale to Fit group. By default, both the height and width will scale automatically. Use the dropdowns to adjust it to your needs. You can also specify how much you want to scale by percentage. Clicking on the lower right corner of this group will open a dialog box for advanced customization options.

You are able to toggle Gridlines and Headings by clicking on the checkboxes inside Sheet Options.

Formulas Quick Guide

There are several formulas. It is such a large part of Excel that it even has its own ribbon. All of them may not apply to your needs, so this will be a rundown on a basic formula that is frequently used.

To begin, a formula starts with an equal sign and is followed by the function. Example: =SUM

The function, SUM, will add numbers within a range of cells. This will not work without knowing what cells are being added. This particular formula may make a quick guess on which cells it needs to add together.

However, if you have to manually include your desired cells, you will add parenthesis after the function with the named cells inside.

Another example: =SUM(A1:A6)

For SUM to add the cells between A1 and A6 it uses a colon to read that as a range of cells.

Tips

Advanced Editing Options

If you need more editing capabilities, click on the lower right corner of the Clipboard, Font, Alignment, or Number groups and a new dialog box will appear. This allows more formatting options and tabs are available to quickly swap to different features.

Auto Save Settings

Need auto-save to happen more often in case of recovering a crashed workbook? Go to File and select Options. A new dialog box will appear. Select the Save tab on the left. Make sure the checkbox is marked for the Save Auto-Recover information section and you can edit how frequently it will auto-save.

Find, Replace, & More

The Find tool offers a dropdown with many options. Initially, if you simply need to find something you can type what you need in the search bar.

In the Find and Replace window, you can refine your search by using the Options button. Using the Replace tab, you can find a word then replace it with a new word you type in. Excel also lets you format within the Find and Replace window.

Sort & Filter is also convenient when working in tables and needing specific information quickly and efficiently. Sort from A-Z or reverse the order. You can customize how your sort data. Filter can include or remove certain content based on your selections.

How to Save

There are multiple ways to save. You can click on the Save icon at the top left corner within the Quick Access Toolbar. Another way is to click on the File ribbon and select either Save or Save As located on the left side of the menu. Saving a worksheet will save over your currently opened file while using Save As will allow you to create a new save.

If it is your first time saving the workbook, a new window will appear and you will be able to select where you want your file to be saved, create a file name, and what file type you will save it as.

You can also save your file as a PDF. Either use the dropdown inside the Save as type: section or go to Export and click on Create Adobe PDF.

Microsoft Office for Students

Most colleges will require students to use the Microsoft Office Suite for assignments and using other software may mess up the format when converting.

A subscription to Office 365 is free for current students on up to five devices. If you are interested, here is the link:

Ask a Librarian