Excel Tips and Tricks for Publishers – CELL TRICKS

This tutorial is part two of the Excel Tips and Tricks for Publishers series. The first tutorial, Excel Basics, is available here.

Cell Tricks

Copying and Moving Data

Select the cell you want to copy, or click and drag to select multiples. You can use Control + C (PC) or Command + C (Mac) to copy the data. Once you click on the cell you want the information to go to, use Control + V (PC) or Command + V (Mac) to paste.

Another way to do this is to use the buttons in Excel, as you have a lot of options this way. Under the Home tab, click the Copy button.

Then select the cell you want to copy the data to, and click the Paste button. Under Paste there is the option for a drop down menu, so that you can choose to copy the formula or the value, bring the formatting, or even paste as a picture. Another option when you paste that appears is the small Control box that appears beside the pasted data. It contains another drop down menu with options for what gets pasted. You can hover your mouse over each option in there to see how it would change things.

Another thing to remember is that if you want to move the data, rather than copying it, you can do the same process as above, except use Control + X (PC) or Command X (Mac), or press the Cut button. This will take the data away from the current location and drop it in where you need it.

Again, either use Control + V (PC) or Command + V (Mac) to paste, or use the Paste button.

Removing Dashes

For many promotions our Marketing & Communications Coordinator requires that you send your ISBNs without dashes in them, but we know that can be a tedious task if you have a lot of titles to go through. Here are some instructions to smooth out that process for you. As you know from above, the best way to move data around without leaving anything behind is using Control + X (PC) or Command + X (Mac). However if you want to take the numbers but not the dashes, you’ll need something a little fancier.

The first step is to make sure that all your ISBNs are formatted as Numbers (find more details or options here from the first Excel tutorial). This step is very important. Though it looks like no changes are being made, if you don’t do this step your end result will be full of errors.

Click Find & Select and choose Replace from that drop down menu, or on your keyboard hit Control + F (PC) or Command + F (Mac). Under the Replace tab type a dash [ – ] into the Find what box. Leave the Replace with box empty and click Replace All. Your ISBNs should all switch to numbers only.

Double check that you didn’t get any decimal points. If there are, you just need to format the cells through the Format box (details also here).

Troubleshooting Removing Dashes

If you find your ISBNs look like the picture below, fear not! You have not lost anything and it is an easy fix to finish the job.

Simply click and drag to highlight the ISBNs that have issues, and in the Home tab under the Numbers box, click the drop down menu that says General. Click Number and your ISBNs should now be full numbers, likely with decimal points.

To fix the decimal points, stay in the Home tab and look for the Cells box. Click Format and then Format Cells in the drop down menu.

In there, under the Numbers category, you can set the decimal points to 0 and voila! No more messy ISBNs.

Merging Cells

If you find you want to merge two cells together, whether for better formatting over a column or for titles, there is an easy way to do that. Click and drag to select all the cells you want merged and under the Home tab, click the Merge & Center button in the Alignment box. There is a drop down menu there for formatting options, though the most common option is on the main button as it formats the data as centered within the cell.

Hiding Cells

If you are working with a lot of data and want to make your sheet more visually manageable, you can hide a chunk of cells temporarily. Click and drag to select what you want to hide, and under the Home tab click Format in the Cells box. In that drop down menu choose Hide & Unhide and then Hide Rows (or Hide Columns if that is appropriate for your sheet). Once you do that, all the cells you had selected will completely disappear so don’t forget about them!

To undo this, or find all the hidden rows, the best way is to use Control + A (PC) or Command + A (Mac), or to click the box in the top left corner of the sheet to select all the cells. Go back to the Format menu, and select Hide & Unhide and choose the Unhide Rows option and everything will appear again.

Freezing Panes

One of the most useful tricks in Excel is freezing panes. There’s nothing worse than scrolling all the way down a giant list only to realize you have no idea which column you’re looking at, or what the numbers mean!

You can freeze panes while setting up your data sheet, or after everything is entered, it won’t alter the data. Make sure that the cell that is selected is directly to the right of the column you want frozen, or under the row you want frozen. If you want both the top row and the first column, you’ll likely want to have cell B2 clicked. You can freeze as many rows and columns as you want.

Once the appropriate cell is selected, go to the View tab and in the Window box is a drop down menu called Freeze Panes. In there you can choose to freeze the rows above or columns to the left, or both. To do both simultaneously simply click the Freeze Panes option and you’re done!

If you make a mistake, in the same drop down menu you will find a new Unfreeze Panes option so you can undo it.


Up Next… Graphs!