Excel Tips and Tricks for Publishers – BASICS

This series is a very basic introduction to using Microsoft Excel as a publisher. Please be aware that these examples are based on Excel 2010, Version 14. There are many resources online for newer and older versions of Excel, so use that to your advantage! Where it is appropriate, both examples for Mac and Windows OS will be given, if only one example is there then it is the same for both operating systems.

Basics

One of the things to remember about Excel is that where you click is important. Clicking on the cell itself means that when you type, you will be replacing whatever is in that box. Any time you are going to type, it is best practice to type in the empty bar above the cells (pictured below) to avoid deleting things you meant to keep. Also when you type anything into a cell, that information is stored in that single cell – so even if the text slides across multiple cells, the only way to access it is through the first cell you clicked on. If you want to merge all the cells that your text touches, find details in the next tutorial here.

Hitting Enter on your keyboard while clicked on a cell will move your selection down, while the Tab key will move right.

Keep an eye on the top row above the cells, as well as the one of the left. The columns are each titled with an alphabet character, and the rows are numbered. If you are entering a formula or command, you will likely need to know which column and row you are referring to in order to make everything work.

Formatting

Numbers

As publishers, your Excel sheets are likely a mix of unit numbers as well as prices. There are ways to make sure that everything is formatted correctly to ensure that your numbers look clean, and can be used in formulas.

There are a few options for formatting your numbers. You can type it in as you want it to be displayed, but sometimes your numbers will automatically have decimal spaces or commas. One way to format your numbers is to highlight the numbers, or click on the column letter that you want changed, and under the Home tab there is a box titled Number where you can choose the format from a drop down menu. Most common ones are Currency or Number.

Another way to format numbers offers a bit more flexibility. Again under the Home tab there is a box titled Cells. Once you have selected the specific cells or column you want changed click Format where you will find a drop down menu, click Format Cells.

In there you can select what kind of number you are using, and choose options for that format. In most cases what you’re looking for is the spot called Decimal places. If you have unit numbers you don’t need decimals so you can set that to 0.

For large numbers, you may or may not want to add a comma between zeroes, which you will find under the Number category.

 

Table Colours

Under the Home tab, there is a box called Styles. In that box is a button with a drop down menu called Format as Table. This menu contains styles of tables with colours and styles built in.

When you choose a style a pop-up menu will confirm the selection of cells to make into a table, which you can click and drag to reselect if need be. You can also check off the box that says My table has headers if that it’s appropriate. When you hit OK on that box, your table will automatically appear.

The colour scheme and layout makes it easier to look at data and figure out what is on the same line. It is also nicer to look at if you’re going to send it out as an invoice or data sheet. The other benefit is that the headers become drop down menus to help you organize the data. You can organize it by alphabet, number size, or by colour if you have changed that.

Formula

For this example, addition is the easiest to explain. Click the cell at the bottom of the list you want to add and under the Formulas tab select Insert Function. You have many options here which you can find in the drop down menu. For this example we’ll use SUM – which is simple addition.

When you click OK, the next menu that comes up is one that selects the data to add. It will automatically grab the cells above where you originally clicked, but if it doesn’t or if you want different data selected you can click and drag to select instead. Hit OK, and the final numbers are already there.

You can also simply click the empty bar at the top and type in the formula if you already know it, and then either type in the appropriate cell distinctions, or click and drag to select them. Once you type in any formula, hit the Enter key on your keyboard to activate it. Remember if you are typing the cells in, use a colon [ : ] to indicate that all the cells in between are to be counted as well – for example adding cells B2, B3, B4, and B5 you would type =SUM(B2:B5)

Here is a handy list of text functions to use in formulas – click on any of them to see more information on how to use them. Some of our favourites include:

Addition: =SUM(ColumnRow:ColumnRow)

Multiplication: =ColumnRow*ColumnRow

Average: =AVERAGE(ColumnRow:ColumnRow)

Repeating formula

In order to use the same formula multiple times, copying and pasting is far too inefficient for big data sheets. The easiest way to copy a formula is to have one example of the formula already ready to go and hover your mouse at the bottom right corner of that cell.

When the cursor changes to a small black cross, click and drag that down as far as you want the formula to be repeated. Alternatively you can double click when with the black cross cursor and it will repeat all the way down until there is no more data.

Organizing

A quick way to organize your data, if you have not already made it into a table that creates the drop down menus, is under the Home menu and the Editing box. Select the data you want to sort based on, and click Sort & Filter to change the order.

By changing the order of the selected cells, each line will change along with it so your data will remain intact. You can sort by A to Z (or Z to A), number size, or a custom sort that you can specify. If you select more than one column, it will automatically sort the one on the furthest left, so watch where you’re selecting!


 

Up Next… Cell Tricks!