Microsoft Office Tips, Tricks, and How-to's to help you use MS Office like a pro!

Carol's Corner Office

Category Archives: Excel 2013

Free Microsoft Excel 2013 articles

How To Have a Worksheet Thumbnail In Your Excel Workbook

Posted on by Posted in Excel 2013

Excel provides a couple of settings that control this feature. First, Excel will let you you control saving of the thumbnail image when you first save your workbook or when you use the Save As command to save your workbook under a new name. The Save As dialog box contains a Save Thumbnail check box at the bottom.

Select the check box, and when you save your workbook the preview image is saved with it. The image represents the appearance of the first worksheet in your workbook. You do not have  control over which worksheet is used in the preview.

On some systems, the Save Thumbail check box may be selected by default; on others not. Whether the check box is defaulted to selected or not selected, is controlled  by a Properties setting.

Below is how you change that setting in Excel 2010 and Excel 2013:

  1. Display the File tab of your Ribbon.
  2. Be certain the Info option is selected at the left side of the dialog box.
  3. Click the Properties link near the right side of the dialog box and then click Advanced Properties to display the Properties dialog box for your workbook.
  4. Be certain the Summary tab is selected.

 

  1. Select Save Thumbnails for All Excel Documents check box at the bottom of the dialog box.
  2. Click on OK to close the Properties dialog box.
  3. Save your workbook.

Please note that the only way to save a thumbnail for an existing workbook that does not have one already saved, is to open the workbook and use the Save As dialog box (click F12 to display it) to re-save the workbook. (Do  not forget to make sure the Save Thumbnail check box is selected before saving.)

It should also be noted that if you save your workbook via macro, there does not appear to be a way in VBA to set this, so macro-saved workbooks are not saved with a thumbnail, and the only way that I know of to save that thumbnail is to later open the workbook and manually go through the Save As steps to save it with the thumbnail.

If you still cannot see the thumbnails in a Windows Explorer window, make sure you have the view in that window set to use medium (or larger) icons. Any other views may not display the desired thumbnails.

How to Quickly Repeat The Last Action in Word or Excel !

Posted on by Posted in Excel 2013

There is a key that is hardly used and yet is a  highly efficient shortcut for speeding up tasks in Word and Excel when you need to repeat the same action, once, twice or many, many times!

That key is the F4 key!

In Word and Excel many tasks can be repeated instantly by tapping the F4 key for as many times as you would like to repeat your last action.

Say you would like to highlight something – do it once and then click F4 to repeat that action.

BONUS: This works not only in Word and Excel but in other Office applications as well!

BONUS BONUS: As you all know, I love to save time and keystrokes!  You can also achieve this by clicking CTRL | Y!

How To Quickly Print Without Opening in Excel

Posted on by Posted in Excel 2013

Should you need to quickly print the contents of a workbook without opening the workbook yourself manually, you can absolutely do this!

Follow the steps below to learn how:

  1. Use the Explorer, My Computer or any Open dialog box to locate the worksheet that you would like to print.
  2. Right-click on the file. Windows will display a Context menu.
  3. Select Print from the Context menu.
  4. Windows will load Excel if it is not already open, open your workbook, and print it. The workbook will then closed.

The only thing printed in your workbook is the single worksheet that was selected when the workbook was last closed. It is also printed to your current designated printer.

If you would like to print using a different printer or area of your workbook then you must first open the workbook in order to print.

Quickly & Easily Change The Location Of Your Toolbar in Excel

Posted on by Posted in Excel 2013

You can quickly and easily change the location of Excel toolbars to whatever works for you!

Follow the steps below to learn how:

Just double-click your mouse on any portion of your toolbar that is not occupied by a tool. The toolbar will be removed from its normal location and will appear in its own dialog box.

Once in that dialog box format, you can easily drag a toolbar to any location on your screen. If you approach a side of the screen, the toolbar will dock to that side. You can dock toolbars to any of the four sides of the screen.

You can experiment with the optional toolbar locations to determine which best suits you.

How to Sequentially Input Data in your Excel Worksheet

Posted on by Posted in Excel 2013

It is not out of the ordinary to need to enter a series of numbers within a range of worksheet cells. For example, you may need to enter a series of numbers in the first ten columns of a certain row, or you may have a need to enter information just in a range of ten cells in a particular column.

To sequentially enter information in a range of cells, you should always first select your cells. You will notice that Excel will leave the top-left cell in the range as the input cell (it appears white and outlined). The rest of the cells in the range are shaded, to show that they have been selected.

Now all you need do is start entering your numbers. When you do, the value you keyed in is entered into the input cell. When you click Enter at the end of the value, Excel will save the value and move the input cell to the next cell in the selected range. Excel will move the input cell either left to right, top to bottom or top to bottom, left to right.

Good to know right?

How To Replace Dashes With Periods in Excel

Posted on by Posted in Excel 2013

Suppose you need to change the appearance of the data in your worksheet.  For instance, you may want to replace dashes in a text string with periods. For example, if you have a phone number formatted as 235-456-7890 you may want to change it to 235.456.7890.

This is very easy to do using the SUBSTITUTE function:

=SUBSTITUTE(A3,”-“,”.”)

The end result is that your dashes appearing in the string in cell A3 are replaced with periods.

Told you it was easy!

Thank you to Allen Wyatt.

How to Change the Color Inside a Shape in your Excel Worksheet

Posted on by Posted in Excel 2013

Once you have created a drawing object, you can fill it with your choice of color. Excel makes this very easy to do.

Follow the steps below to learn how:

  1. Select the shape you would like to fill.
  2. Be certain the Format tab of your Ribbon has been selected. Please note that the Format tab will not display unless you select your shape.

  1. In the Shape Styles group, click the Shape Fill tool to display a color menu.

  1. From the color menu, select the color you would like to use.

  1. If you do not see a color you like, click on More Fill Colors to display a wider selection.

Once you have selected  a color, Excel will remove the color menu and fill the interior of your shape with the color you chose. To remove a color, you can repeat the same steps, but in step 4 select No Fill.

How to Quickly Select Entire Columns or Rows in Excel Worksheets

Posted on by Posted in Excel 2013

It is always helpful to know both the mouse and keyboard methods to accomplish the task at hand in Excel. Personally, I like to use my keyboard because that is usually where my hands are already!

Below are a few keyboard shortcuts that you will find yourself using all the time – selecting columns and rows.

Follow the steps below to learn how:

  • To select the entire row where your active cell is located, simply depress your Shift key and click on your Spacebar.
  • To select the entire column where the active cell is located, depress your CTRL key and click your spacebar.

It matters not which cell is active when you do this.

  1. You can also select multiple rows and columns using your keyboard:
  2. If you want to select rows 1 through 5, use your arrow key to move to any cell in row 1.
  3. Now depress your Shift key and use the down arrow to select down to row 5.
  4. Click Shift + click Spacebar.

The entire rows of 1 through 7 will be selected.

To select columns A through E, use your arrow key to move the active cell to column A.

  • Depress your Shift key and press your arrow key to select over to column E.
  • Click CTRL + Spacebar.

Now columns A through E will be selected.

How to Get Rid of Empty Rows in an Imported Worksheet in Excel 2013

Posted on by Posted in Excel 2013

There are times when you have to work with data in a worksheet imported from another program into Excel.  Normally, it is not a problem to import the worksheet. The problem is that during the process potentially hundreds of extra rows that have no data in them are imported as well. After the import you have to manually delete those extra rows so you can use the rest of the data. So is there is a way to easily get rid of these empty rows?

There are different  ways you can look at this. The easiest way could be to simply sort your imported data by the column of your choice. All the rows that contain nothing in that column end up at either the end or beginning of the data (depending on if you sort in ascending or descending order) and you can easily delete those rows.

When you do a sort in this manner though, you could easily end up with your data out of the original, imported order. If you need the data to be in the original order—but with the blank rows removed— you can just insert a column to the left or right of your data, fill it with sequential numbers, do your sort by any column except that added column, and then delete the rows that are blank (with only something in the numbering column). You can then sort a second time based on the numbering column and your data will be back in its original order. Seems a tad tedious don’t you think?

Follow the steps below:

  1. Select your entire column.
  2. Click F5 to display the Go To dialog box.
  3. Click Special to displays the Go To Special dialog box.

Go To special

  1. Select Blanks | OK. Excel selects only those cells in the column that are blank.
  2. Select Delete from the Edit menu to display the Delete dialog box.
  3. Select Entire Row and then click OK.

How to Create A Workbook Clone in Excel

Posted on by Posted in Excel 2013

There may come a  time when you would like to make a copy of your workbook, without affecting the original. Excel does provide an easy method to do this.

Follow the steps below to learn how:

  1. Display the Open dialog box. (If you are using Excel 2007, click Office button | Open. If you are using Excel 2010 , click File | Open. If you are using Excel 2013, click File | Open | Computer |Browse.
  2. Select the workbook you would like to make a copy of.
  3. Click on the down-arrow on the right-hand side of the Open button. Excel will displas a list of different ways you can open the selected workbook.
  4. Select Open As Copy . Excel will  open a copy of the workbook.

The workbook that has been opened will use the same file name, but Excel will attach something at the beginning of the file name, as a prefix. For instance, you might see the file name prefixed with Customers copy or Copy (1). Thus, if the original workbook you selected in above is named Customers.xlsx, Excel will create a workbook named Copy of Budget.xlsx” or “Copy (1)Budget.xlsx.” If you want to rename the file, you will need to either use the Save As command, or rename the workbook after closing it.