creating graphs and charts using excel basics shown on the laptop

Sort Your Life into Neat Little Boxes with These Excel Basics

I've Heard of Excel, But I Don't Know What It Is

Excel is like that one coworker whose name you never learned until it was too late to ask. You've seen Excel around. Maybe you've seen it on a job description or you know it as the little green icon on your computer you never click.

Excel icon with question marks around

Image via Pixabay (altered)

What does it actually do though?

Excel is part of the Microsoft Office collection of applications designed to for the workplace. You may be familiar with some other apps in the collection like Word or Powerpoint.

These other apps are self-explanatory. Word helps you write words. Powerpoint helps you make your point, powerfully. Excel is the weird one.

What does it help you excel at? Does it make you faster? Does it make you smarter? Does it make you better at movie trivia?

It does all those things. Excel is a tool for organizing data. It gives you a functionally infinite number of little boxes to fill with numbers, words, or any other piece of information you choose.

school teacher giving excel basics lesson

Image via Pixabay

Beyond that, it gives you tools to track, calculate, and predict many ways that information can intersect. When you learn the Excel basics, it takes care of all the small ideas so you can worry about the big ones.

That Sounds Great, What Does It Mean?

Excel lets you build spreadsheets, which consist of numbered columns and lettered rows. It's like a bingo board but a lot more useful (unless you're extremely lucky at bingo).

Typically and in its most simple form, one set of data goes in the columns and the corresponding set goes in the rows. This gives you the power to communicate a connection between two ideas without using words.

We do this all the time without realizing it. Say you're making a shopping list. You wouldn't write out in full sentences everything you need and how many of each item you need.

You would list every item you need and write the quantity of that item needed next to it. Excel makes it possible to do this on a greater and much more complex scale.

It's a simple and effective method of bookkeeping in almost any scenario. It also creates an easily readable and searchable archive of all your data.

Remembering Things Is Boring and Hard, So Why Do It?

Even after learning only a few excel basics, you can create a detailed, living picture of what you have, what you need, and how you're doing.

Let's say you sell bulk circus equipment. Some Bozo calls you and says, “Hey! I bought 1,000 red noses from you two years ago. I just now got around to counting and you only sent 500! I want a refund!”

huge piles of office paper files

Image via Pixabay

This could go two ways. You could search through your paper records for this transaction. It could take a while and you may never find it.

If you knew the Excel basics, however, you could search your archive and within seconds discover that Bozo only ordered 500 noses.

Having a well kept digital archive will save you time and money. It may also keep you in the good graces of clowns, who you do not want to cross.

Put It On My Computer!

Now you want Excel. Why wouldn't you? It's great! How do you get it?

You do the same thing you do when you buy literally anything, you go on the internet!



Excel is available through a subscription plan called "Office 365." For a yearly fee, you get access to Excel and several other apps and services in the Microsoft Office suite.

Go to products.office.com and select the "products" pull-down menu. There, you'll see options for individuals, homes, and offices. Select the plan that works for you and purchase it!

Follow the prompts to download and install Excel and you're ready to go! Before you work with Excel, take time to familiarize yourself with the app.


Don't be Basic, Learn the Excel Basics!

It's a familiar scene. You're at the watercooler. That spreadsheet hotshot Brian is talking about a beautiful budget proposal he put together and he is NOT humble about it.

Small business meeting in the office

Image via Pixabay

Everyone is talking about his formatting and vibrant use of shading and you're worried what people will think of you if you don't chime in soon.
You open your mouth and a disastrous faux pas falls out.

You confused a workbook with a worksheet and everybody heard you. You've revealed yourself to be an Excel poser. Plus you KNOW Brian will not let this go, he'll be calling you “worksheet” for weeks. If you only you'd studied your Excel basics.

Here's a handy list of Excel terms and definitions to make sure this chilling scenario doesn't happen to you.


Terms



Definition

Workbook

an Excel file. This is the main .xlsx file that contains every piece of data you've entered

Worksheet

A single page within your workbook. These are the actual spreadsheets in your file

Workspace

The specific arrangement of windows in your workbook

Columns

The numbered vertical alignment of cells on your worksheet

Rows

An individual box where you enter data on your worksheet

Headings

The numbered or lettered grey area of your columns and rows

Template

A workbook that has already been formatted to a specific need

Formatting

The visual appearance of cells

Conditional Formatting:

Formatting that applies only to cells which meet the criteria you have set

Formula

A sequence in a cell that defines the interaction between data in other cells and produces the value that results from that interaction

Formula bar

A bar above the worksheet that displays any formulas in the active cell

Ribbon

The space above the formula bar with tools and other options

Filter

A rule you can set to determine which cells in a worksheet are displayed

Freeze Panes

A group of columns or rows that may select to stay visible, even if you scroll away from them

A Fresh Start

You've downloaded and installed Excel. You learned some terms. Now it's time to learn the Excel basics.

Open the "File" menu on the top left of the screen and select "New."

From here you can watch in-app tutorials and select from thousands of pre-made templates, or you can make like Fleetwood Mac and go your own way by selecting "Blank workbook."

Click on "Blank workbook." That will open a blank sheet filled with nothing but potential!

Fill Those Cells with Your Sweet Sweet Words (or Numbers)

The only people who like empty boxes are cats and kids with big imaginations, so let's fill them up!

Entering data is simple, just double click the cell you want to type in and type in it! Try it out with one of your favorite words or phrases, treat yourself.

excel document with “cellar door” written in one cell

You may find that the box is not wide enough for your input, but don't worry! Like the maternity pants you always wear to Thanksgiving dinner, you can adjust the width.

Go to the top of the column where you see the letters and hover over the line between them. Your cursor should turn into a line intersected by two arrows. Click and drag the columns' borders in the direction you choose.

excel document with columns of varying width

Make It Pretty

People are shallow, they'll ignore the most important information in the world if it's ugly. So let's learn a little about the cosmetics of spreadsheets. Aesthetics are part of the Excel basics.

A new paint job can take a wall from drab to “dang! That looks good” and the same principle applies to the cells on your spreadsheet.

To apply cell shading, first select the cells you want to shade. You do this by clicking on one, holding it down, and dragging your cursor over the rest.

excel document with different shades of color on three cells

In the home menu, find the paint bucket icon and click the arrow next to it. This will bring down a selection of color options.

If you don't see the color you want, don't worry! Click “more colors” to bring up a box with a greater selection under the “Standard” tab and the full RGB spectrum under the “Custom” tab.

Consider your audience and the purpose of color for these cells. Choose a nice red to grab their attention, soft baby blue to put them at ease, or the exact shade of chestnut brown that matches the hair of someone they loved and lost to make them nostalgic and emotional.

color tab showing different options of color model in excel

You can use the same method to change the color text in a cell or cells by selecting the "A" next to the paint bucket.

To get rid of shading, use the same method but instead of selecting a color, select "No fill."

Save Early, Save Often

You've spent hours entering data and perfecting the look of your workbook. It's finally ready to show your boss and then …

Oh no.

No no no no no no no

Brian, that show off, was bragging about his latest fantasy football win, he ran back to go catch an imaginary pass and he knocked your laptop right out of your hands.

The screen goes black when it hits the floor. After several terrifying minutes, your computer comes back on. Miraculously, it still works. You smile until you remember.

You didn't save your work. You've lost the perfect spreadsheet.

man using his laptop to create basic excel worksheet

Image via Pixabay

All you had to do was click file, then save, then choose your destination, and name the file. You even could have cut out the first two steps by clicking on the floppy disk icon at the top of the screen. There's no point in learning the Excel basics if everything gets erased.

excel home screen showing autosave icon

You can also get around this whole problem if you have Microsoft OneDrive installed. In that case, turn on auto-saving using the switch directly next to the floppy disc icon. With this feature, your workbook will save automatically.

Let's Play with Ribbon

Everybody loves ribbon, from overzealous moms wrapping presents at Christmas to cats

In Excel, the ribbon is the row of menu buttons and the tools and options that appear when you select each one. Let's get into the functions of some of those buttons.

We've already covered several of the buttons in the file menu, which is first on the menu bar. The second button the menu bar is selected by default because it's the only one you need to master the Excel basics. It says "Home."

home screen of excel showing different options to make an edit

Makin' Copies

The home menu has seven parts. Going left to right, the first one contains the copy and paste options.

autosave screenshot of excel

The clipboard icon is your basic paste. It takes whatever you've copied and places it in the active cell.

There are two different ways to do this, which you can change by clicking the arrow underneath the clipboard. “Keep source formatting” will paste the content you copied with the same formatting in which you copied it.

“Match destination formatting” will paste the content, but conform the formatting to the cell you are pasting it to.

Next to that is the scissors, but they're not for cutting paper or hair. They're for cutting ideas. Clicking this will copy what's in the active cell and then delete it. This is ideal for moving something from one cell to another.

two men carrying a long block of wood in silhouette form

Image via Pixabay

Below the scissors, you'll see two pieces of paper. This is the copy option, it will copy what's in the active cell, but not delete it. It's great for duplicating content.

Finally, we have the paint brush, below the paper. Click this to both copy and paste only the formatting of a cell without copying the content.

Clicking the arrow next to the word “clipboard” at the bottom will show you what you have last copied.

Stylin' and Profilin'

The second section of the home menu allows you to change the font and style of cells. Remember when I said aesthetics were part of the Excel basics? Here's where you can take more precise control of the look of your workbook.

font style and font size dropdown menu on excel

The bar controls the font. You can choose whatever font you like for your work, but remember: nobody likes Comic Sans.

Next to the font bar, there's a box with a number in it and next to that there's a big A and a littler A. These control the font size. Choose any number you want in the box or increase and decrease by one at a time with the A buttons.

Under these you'll see the letters "B," "I," and "U." Clicking "B" will make highlighted words bold, so readers will hear them loudly in their head. "I" puts highlighted words in italics. Readers will hear these like a whispered secret. "U" underlines highlighted words. This will tell the reader you're serious.

Next to those you'll see a box with a grid. This sets the color, style, and thickness of your cells' borders.

borders dropdown page on excel

Clicking the grid button itself will apply the current border settings to the active cell. Clicking the arrow next to it will give you options. All the options have illustrations next to them, so pick the one you like.

Clicking the arrow at the bottom of this section will open the cell formatting menu, which has all the options described above and more!

To the Left, to the Left (or the Right or Top or Bottom)

Like a bad Dungeons and Dragons character, your text can change alignment at any time. The third segment of the home menu controls these options.

text alignment options on excel

On the left side of this segment, you'll see six buttons with a collection of three to four straight lines. The top three buttons control the vertical alignment of the text in a cell, the bottom three control the horizontal alignment.

Next to these, you'll see the letters "ab" with an arrow under them. This allows you to rotate text. It's a nice formatting option, but it can increase the size of your cell. Act like a nervous DJ: spin cautiously.

Underneath the rotate button are two that look a lot like the alignment buttons. Use these to increase or decrease the amount of indentation in your cell. You can put your words square in the middle of a cell so they don't get claustrophobic or have them hug the borders if they need a friend.

wrap text and merge options screenshot on excel

Next to that, you'll see the words "Wrap Text" and "Merge & Center." If your text does not fit in the cell and you don't wish to make it wider, "Wrap Text" will stack the words on top of each other and increase the length of the cell if necessary.

screenshot of how to change the size of a cell

Maybe you don't want to change the size of your cell at all. That's reasonable, changing the size of one cell will create a lot of ugly empty space in all the other cells in that row or column. "Merge & Center" is a great way to avoid that.

"Merge & Center" combines two or more cells into one and places the text in the middle. In other words it ...

Merges and centers them. It seems obvious in retrospect. If you want to merge without centering, click the arrow next to the button for more options. This is also where you unmerge cells. These are the Excel basics that get overlooked.

The arrow next the word "alignment" at the bottom takes you to the same menu as the font arrow.

Intro to Mathematics

Math nerds rejoice, it's finally time to talk numbers. The driest part of the Excel basics, but it's also what Excel does best. The fourth segment in the home menu controls the format in which numbers will appear in your worksheet. You can set the format for the overall sheet or for individual cells by highlighting them.

general view of setting on excel

At the top, you'll see a bar that says "General." This sets the general format. Click on it and you'll see your options. Numbers will appear differently if you're dealing in money, dates, times, percentages, etc.

Below that bar, you'll see five buttons. The first defaults to a dollar sign (assuming you live in a country that uses dollars). Click on this to change the format of currency. You'll see a drop-down menu with symbols for pounds, euros, yen, and a "more accounting options" button. That button will bring up a menu with all the currencies of the world.

Next to that you'll see the percent symbol, this is a shortcut to the same button in the general drop-down menu.

Then you have the big comma button. This will format your numbers so have commas in them so that numbers go from 6000 to 6,000. This button saves both time and strain on your middle finger when writing out a lot of large numbers. There's nothing worse than a middle finger injury because you can't even register your displeasure with the thing that injured you.

Next to the comma you'll see zeroes and arrows. These add or subtract decimal places to your numbers. You can add a hundred decimal places for extreme precision or play it fast and loose by taking them all away. There's room for all styles within the Excel basics.

The arrow next to the word "numbers" once again takes you to that same formal cells menu as both the "alignment" and "font" arrows.

Stylin' and Profilin' Part II

Enough nerd stuff, let's get back the fun part of the Excel basics. The fifth segment of the home menu is like Paris during fashion week: full of style!

formatting table options on excel

This one's simple, only three buttons.

The first one is "Conditional Formatting." You may remember those words from the handy chart up above. You can set rules which will automatically change the formatting of certain cells. You can do this with numbers above or a below a certain threshold, dates within a certain range, and more.

For example, let's say you want to make the most recent information pop. You would click the "Conditional Formatting" button, hover over "Highlight Cell Rules" and click "A Date Occurring..."

an example of conditional formatting in a cell

This brings up a menu that says "Format cells that contain a date occurring:" with two bars below it. In the left bar, select "Yesterday" and in the right select "Light Red Fill with Dark Red Text." Any instance of yesterday's date will automatically change to this format. This will stay consistent whenever someone views the file. Call yourself Dr. Frankenstein because you've brought life to your document, it can think and change as time goes by.

Let's Table This

This next button is a doozy that's why it's getting its own section. It's a robust piece of software, so even the Excel basics can get a little complicated. The second button in this segment says "Format as Table". Excel tables organize your data like dinner tables organize food, they give you a nice pretty plane to put it all on.

sample formatted table on excel

Tables can be made up of the following parts ...

Header Row

By default, tables have header rows. This is a row of cells at the top of your table that labels the columns and allows you to sort and filter your content. We'll go deeper on sorting and filtering later.

Banded Rows

The banded rows are all the other rows. The alternate in color to improve readability. This is a good opportunity to have fun but still look professional. You can make them tiger striped!

sample table with its header colored as orange

Calculated Columns

If you type a formula (again, more on those later) into one cell in a blank column, it will automatically spread to the rest of the cells in that column. That's a calculated column.

Total Row

This is a row at the bottom of the table that calculates the totals of each of the columns.

Sizing Handle

The sizing handle is the little mark on the bottom right corner of the table. Drag this to change the borders of the table.

Okay Where Were We?

Now you know what a table is, let's get back to the Home menu. The button next to "Format as Table" says "Cell Styles." This is your walk-in closet, it gives you even more style options for your cells.

cell styles option on excel

This menu suggests different shading styles to communicate different things. It also lets you select gradients of color. When full color is too powerful, you can use the more subtle touch of a 20, 40, or 60 percent accent. You're learning the Excel basics, but they give the tools to be complex.

Let's Get Some More Cells in There

The sixth segment in the home menu lets you add and subtract cells. It also gives you another way to format them.

cells menu options to add or remove cells

"Insert" will add a row of cells above your active cell, giving you a little more head room if you need it. Clicking the arrow on the bottom of the button gives you the option to add rows or columns and in different places.

Like the proverbial good Lord, Excel can both giveth and taketh away. "Delete" removes cells in the same way "Insert" adds them with one difference. Instead of deleting the row above your active cell, it deletes the row your active cell is in. Be careful with this one and remember: if you delete the wrong cells, a quick press of Ctrl + Z (or ⌘ + Z on a Mac) will bring it back as long as you have done nothing else since you deleted it.

This also applies to any singular incorrect move in Excel.

The final button is "Format." This button is another way to change the size and shape of cells like we already described, but unlike an old dog, it also does some new tricks.

Click the arrow on this button and you'll see those familiar ways of altering cells, but you'll also have the option of renaming your worksheet. Give it a nice name like "William" or "Susan." You could also name it something professional like "Budget" or "Calendar".

altering cells on excel tabs

You can also change the color of these tabs. It's not the most prominent part of the worksheet, but with the Excel basics, you can change the look anything in the window.

Cut the Fat!

I know. Your work is precious. After spending all this time on your sheet, cutting anything is heartbreaking but the more you work on something, the more chances it has to get bloated. The seventh and final segment on the home menu is "Editing" and it can help with that.

editing tab with autoSum option

The first button in this segment says "AutoSum" and you know what that means ...

It's Time to Talk Functions

This is another big one so get ready. Let's talk about formulas, functions, what they are, and the difference between them.

In Excel, a formula is an algebraic equation you put in a cell to generate numbers.

Nobody likes to think about algebra when they're in school so you especially don't want to think about algebra now you're out of school. Don't worry, it's not as scary as it seems and this is probably the hardest part of learning the Excel basics so it won't get worse than this.

Here's a simple example with the most basic formula there is: AutoSum.

If you enter the value of 2 in cell A1 and the value of 2 in A2, you can enter =SUM(A1:A2) in A3 and when you hit enter, Excel will do the math of adding the values in A1 and A2 and display the number 4 in A3.

how AutoSum formula is done for adding values of cells

Formulas always start with an equals sign. This lets Excel know that whatever follows that = is a formula.

In love, it's up to you and your partner to define the relationship. In Excel, the word immediately after the equals sign defines the relationship between the cells listed in between the parenthesis. So in this instance, "SUM" tells Excel to add those numbers.

There is no limit to the number of cells you can place in between the parenthesis. You can do this by entering the coordinates of the cells, which has a fun Battleship vibe or you can select them with your mouse while the formula is open, which is typically faster.

Don't enter every individual cell's coordinates! If you wanted to select A1 through A100, you wouldn't type "A1:A2:A3:" and so on. It's tedious and will not work. Instead, type "A1:A100" It will automatically include all cells in that range.

Congratulations! You've learned your first formula AND your first function. A function is a formula that's pre-programmed into Excel. Here's a list of the most popular functions in Excel. This is a cornerstone of the Excel basics.


Terms



Definition

SUM

Creates a sum of values in the selected cells

AVERAGE

Calculates the average of the values in the selected cells

COUNT

 Will count the number of cells that have a numerical value in them

MAX

 Will return the highest value in the selected cells

MIN

Will return the lowest value in the selected cells

IF

Displays one value if a condition is met and another if it is not

LOOKUP

When given the value of a single row or column, will return the value in the same cell in another row or column

MATCH

Searches a range of cells for a specific value and returns to relative position of that value within the range

DAYS

Will return the number of days between two dates

Okay, Back to the Ribbon!

The first five functions in the chart are all listed in the AutoSum drop-down menu. Clicking on them will save you the trouble of having to type them out.

AutoSum dropdown menu

Under the AutoSum button is the Fill button. This is another tricky one!

Clicking on "Fill" results in a drop-down menu with the basic options "Up," Down," "Left," and "Right" and more advanced options below that.

The Fill option takes a value from one cell and automatically moves it to another. There are two important things to remember here!

1. The cell being filled is always the active cell. So "Up" doesn't take the value of the active cell and move it up one cell like you might assume, it takes the value of the cell below the active cell and moves it up into the active cell.

2. Formulas are relative. "Fill" won't copy the same values, it will copy the relative position of the values in the formula. So if you're moving the formula one cell up, the values in the formula will also move one cell up.

Time for Spring Cleaning

Unless it's not spring, then it's time for Summer, Fall, or Winter cleaning. Beneath the Fill button is the Clear button. Pressing Delete or Backspace on a cell deletes the contents of the cell. Pressing those buttons on an active cell deletes the contents one character at a time but neither deletes anything else.

man fixing and sealing his garbage in a garbage bag

Image via Max Pixel

"Clear" brings up a drop-down menu where you can also delete things like formatting, comments, hyperlinks, or everything at once.

clear dropdown menu showing options to delete formatting, comments and the like

I Need to Get Organize

Next to "Fill" is "Sort & Filter" and you better be ready for another drop-down menu. By now you should know that drop-down menus are the life blood of the Excel basics.

sort and filter dropdown menu

"Sort A to Z" or "Sort Smallest to Largest" will sort a selection of cells in alphabetical order if they're words or numerical order if they're numbers. "Sort Z to A" or "Sort Largest to Smallest" does the same thing in reverse. If you want to go wild, you can create a custom sort by clicking the "Custom Sort" option.

Below that is the "Filter" button. Click on this and arrows will appear on top of all your columns. This gives you access those same sorting options above but also includes the secret number filters. These are the more advanced methods of sorting that only apply to numbers.

text filter dropdown menu

Finally, we have the Private Detective's favorite button "Find & Select," represented by a magnifying glass. In this drop-down menu you can find specific values, replace them with other values, or go to a specific cell by typing in its coordinates.

One Last Thing ...

I hope you've enjoyed your time with the Ribbon because it's the biggest part of learning the Excel basics. Before we say goodbye to the Ribbon, we must learn to ... say goodbye to the Ribbon. Clicking the small arrow on the bottom left of the Ribbon will collapse it. If you do this on accident, don't panic! You can bring the Ribbon back by clicking the box in between the minimize button and "Sign in" on the top bar of your window.

Do All This But Faster!

Now that you know the Excel basics, it's time to learn the keyboard shortcuts so you can do them in a flash. Here's your last chart

gear ison

Image via Flaticon

General

  1. - Ctrl + N: New workbook
  2. - Ctrl + O: Open workbook
  3. - Ctrl + S: Save workbook
  4. - Ctrl + P: Print
  5. - Ctrl + W: Close workbook
  6. - Alt + F4: Close Excel (⌘ + Q on a Mac)
  7. - Ctrl + A: Select all cells
test results icon

Image via Flaticon

Editing

  1. - Ctrl + Z: Undo previous action
  2. - Ctrl + Y: Redo previous action
  3. - Ctrl + C: Copy selected cells
  4. - Ctrl + X: Cut selected cells
  5. - Ctrl + V: Paste from clipboard
spreadsheet icon

Image via Flaticon

Tables

  1. - Ctrl + T: insert table (^ + T on a Mac)
  2. - Alt + ↓ : Activate filter (⌥ + ↓ on a Mac)
  3. - Shift + Space: Select table row
  4. - Ctrl + Space: Select table column (^ + Space on a Mac)
  5. - Ctrl + A: select table

Mac users note that unless stated otherwise, substitute the Ctrl key for the Command key in your shortcuts.

Now Go Out There and Make Spreadsheets!

With these Excel basics under your belt, you can wield worksheets like a champion!

laptop with karate uniform and belts on the side on top of the table

Image via Pixabay

If you want to learn more than just the Excel basics, you can go straight to the source. The Microsoft website has articles and tutorials for everything and anything you can do in Excel.

Related Posts Plugin for WordPress, Blogger...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.