Category Archives: Excel

AutoFill with a Custom List in Excel

Create your Own Lists to Work with AutoFill

You may already use some of Excel’s AutoFill capabilities to complete names of months, days and years and/or to copy formulas, so you know it is an amazing time saver. Before we explore creating your own custom lists, here are a few tricks to tame the AutoFill feature:

Excel just needs to see a pattern to fill out the remaining cells. Common sequences only require one entry, such as Monday or Friday, to have the following days of the week automatically fill as you drag the icon down or across. (The Fill handle is on the bottom-right corner of the cell border – a small, black plus sign). The same is true of months or quarters. Excel automatically inserts the next entry and repeats the pattern if you continue to drag past the ending entry.

Trick: If you have data in the cells to the left of the column where you want to use the Fill handle, just double-click on the Fill symbol in the cell that you want copied, and results will fill in down through that number of rows.

Sometimes, AutoFill hiccups when you ask it to repeat a pattern, as in a numbering sequence. For instance, if you type a “1” in a cell and try to AutoFill, you will get only 1’s in the copied cells. You must put in a second number, select both cells, and then AutoFill for Excel to get the pattern. You can enter “1” in a cell and then “2” in a cell below or to the right, and you are off and running. This also works for step-numbering, i.e., enter “1” in first cell and then “5” in second cell, select both and AutoFill. (Same is true for years).


Trick: Save yourself some typing and mouse action:

  • Enter the first number
  • Hold down CTRL key, point at Fill Handle and drag down (or across)
  • Presto, you have sequential numbers (or years). For step-numbering, you still have to type and select at least two entries.

Define your own series that AutoFill can use:

You can enter any list of entries in an Excel worksheet, select the cells and drag down or across and that pattern will be repeated but if the series is one that you want to use in other worksheets or workbooks, create your own custom list:

  • Click File tab on the ribbon and click Options at the bottom of the category list.
  • Click Advanced and then scroll down near the bottom to the General area.


  • Click the button Edit Custom Lists… Excel displays the Custom Lists dialog box.


  • Select NEW LIST in the Custom Lists list.
  • In the List Entries box, start typing the items in your fill series, in the order they should appear. This could be products or department names or list of employees in alphabetical order. Press Enter after each entry.
  • When finished click the Add button.
  • Click OK to close the dialog box.
  • Click OK to close the Excel Options dialog box.

Your custom list is now set up for AutoFill. Just type an entry from that list that you want to start with, select the cell, and then drag the Fill handle. Excel fills the selected cells with the items from your custom list in the same order created.

Create as many custom lists as you like and you now have your own personal quick entries!


The Magic of Excel Tables

 

Table Benefits

This terrific Excel feature does many things automatically including formatting, formulas and much more. It is very easy to convert worksheet data to a table and convert back to a worksheet range with one mouse click, if needed. (Note, prior to the 2007 version, Tables were called Lists). 

Here’s some treasures you get by Inserting a table:

  • Creates a sophisticated worksheet in a flash. Inserting a table auto formats your data with the built-in style and applies filtering to each column for easy data analysis.
  • Auto selects all the data for the table range. (Make sure you have column headings and don’t have blank rows or columns in your data. Insert a blank row if you don’t want all data in the table).
  • Instant access to a Total Row function where columns can be summed, averaged, counted and more.
  • Insert Table Rows and not affect data cell referencing in columns outside of the table.
  • Easy to select entire data or column or row no matter how large the table.
  • You can name a table and use it in a formula.
  • Change the table style with one mouse click. Live Preview displays how your table would look as you point at each style.

Create a Table in a Flash

You have checked the worksheet and removed any unwanted blank columns and rows:

  • Click anywhere in your data
  • Click the Insert tab on the Ribbon
  • Click the Table icon in the Tables group (Excel will display the Create Table dialog box and auto insert the cell range but be sure there is a checkmark in My Table has headers)

Presto! Excel has applied its default Table format and inserted filters for each column.

When you really want to impress or in a hurry, just click anywhere in your data and press CTRL T to auto insert your table!

Select the Table

Tables make it a snap to select all the data with or without the column headers:

  • Point your mouse to the very upper left of the very first table cell (column header).
  • When mouse pointer changes to a small, black down-angled arrow, single click for data only, or double-click for entire table. (If you double-clicked too slowly and only highlighted data, just single click and now column headers are included).

Customize the Table

Add a column to the table or click below the last row and enter data, and the table is automatically expanded along with the formatting to include the new cells.

Add the Total Row feature. Click in any cell in the table and then, on the Ribbon, under the Table Tools / Design tab in the Table Style Options group, click in the Total Row box to automatically add a Total Row and sum to bottom of the table. Click in any cell in that new row to display a drop down arrow and choose a function, i.e., average, count, etc.

Table Restrictions

A couple of things to be aware of in the table:

  • You cannot create Groups and Outlining within a table. The SUBTOTAL function that is used at the bottom of each column allows selection of function formulas such as SUM, AVERAGE, COUNT, etc., that apply to the entire column.
  • You cannot share a spreadsheet that contains a table.

Convert a Table Back to a Range

In a hurry to share or need to group your data? The good news is you can use the one click method to convert to a data range:

  • The Design contextual tab for the Table is displayed on the Ribbon.
  • Click Convert to Range in the Tools group. (Excel asks if you really mean it).
  • Click Yes, and you are back to the data range but the table formatting style and the column filtering is still applied. This can be handy because it still has those table benefits but you can now group and outline and/or share the worksheet.

Remove Table Formatting

If you want to be back to Square 1 so the table formatting and filters are completely removed, you have  some choices depending on whether you apply before or after Convert to Range, and if you want to retain your font and numerical formatting.

After converting table back to a range: The table style and filters are still applied.

  • Ensure all relevant data is selected.
  • Click the Home tab, and then click the Cell Styles arrow in the Styles group, and choose None.
  • Another option is to click the Clear arrow in the Editing group and choose Clear Formats.

Be aware that both these methods remove the table formatting style but you may have to reformat font and numerical formatting.

Want to have all the formatting back to its state before you inserted the table? Apply these steps to remove all formatting before you Convert to Range.

Before converting table back to range:

  • In the Design tab on Table Tools, click the More drop down arrow button in Table Styles group.
  • Choose the first icon under Light which is None.
  • Click in the Filter button icon in the Table Styles Options group to remove the filtering icons from the columns.
  • Click Convert to Range in the Tools group and confirm.

You now have your spreadsheet back just as you left it. Experiment with tables. I think they will save you time and frustration, especially in those humongous worksheets!

 

 

 

 

 

 

Quick Navigation in Excel Worksheets

Go There in a Flash in Excel

You inherited the biggest spreadsheet on the planet and have to scroll and troll to ranges miles away from your current screen! Don’t despair, Excel has several tools to get you there quickly…

The Go To Dialog Box

You can jump to any area of your worksheet with this handy command. Using the Ribbon, Home tab, Editing group, Find & Select drop down arrow, and select Go To… Just type in a cell reference, click OK or press ENTER, and you are there!

OK, that might beat scrolling but not by much, so here’s the corresponding shortcut:

Press CTRL G or F5 function key to auto display the Go To Dialog Box and it is ready for your entry. It also remembers your previous cell location(s).

The Name Box

Want the quick mouse navigation trick? The Name Box is your ticket! Just click in the box and type the cell reference (such as T44456); press ENTER and you are there. (Unfortunately, there is no keyboard shortcut for the actual Name Box but you could use F5 to display the Go To box, type the cell reference and press ENTER).

keyboard angled 2 x 3
Where did I put that?

Range Names

The amazing Name Box is multi-talented. We saw we can highlight large ranges of cells and also jump to a faraway cell location just by typing the cell reference(s) in the Name Box. But what if you are short on sleep and can’t remember the cell reference(s)? No problem. There is a reason it is called the “Name Box”. All you have to do is find that cell once, select it and give it a name that makes sense to you, like Sales or Profit, etc.

Steps:

  • Navigate to desired location
  • Click on desired cell (or range of cells)
  • Click in the Name Box and type a name, i.e., Sales
  • Press ENTER

To return to that location any time, just click the drop-down arrow on the Name Box and select the name you gave it, and you are magically transported there!

Important things to know when naming a cell:

  • Range Names must start with a letter but can contain numbers.
  • You can use more than one word to name a range but it cannot contain spaces.
  • Capitalize the separate words, like TotalSales, or the underscore can be used to represent the space, i.e., Total_Sales.

Range Names are ABSOLUTE by default, meaning the name will always refer to the cell even if the content is moved. And, even more impressive, Range Names can also be used in formulas instead of cell references. That Excel is one smart cookie!

The New Start Screens

2013 Office applications sport a new color-coded start screen:

  • blue for Word,
  • green for Excel
  • orange for PowerPoint
  • green for Publisher
  • burgundy for Access

They all look and behave the same way as this example for Word. Launching the program displays a list of recent documents. A blank document is the default option or instead you can select a template, search online for templates by clicking the Category links or typing key words in the Search Box.

Click Open Other Documents to search for a document on your computer or in a OneDrive folder. The top right of the screen shows details of the OneDrive account that you are currently logged in to use and you can Switch User or access Account Settings with the drop down arrow next to the login name. This is accessible in the same area in all your Office programs, and inside of each of your files.

word-excel-powerpoint-shortcuts-tips-start-screen-1

Start screens help new users find their way around more easily, and experienced users may like having all of their options in one place at startup but not everyone is doing the Snoopy dance here. Turning off the Start Screen will display a blank document when you open the program (just like the old days)!

How to turn off the Start Screen

Word is open:

  1. Click File tab, then Options at the bottom of the category list on the left
  2. The General tab will display
  3. Scroll down to the Start up options section
  4. Remove the check mark from Show the Start screen when this application starts
  5. Click OK and you are good to go.

Test by closing Word and re-opening. The blank document awaits!

Restore Direct Response to Open and Save As

Several 2013 programs display the Backstage when you open or save a file. If you want to bypass it for direct access to the Open and Save As dialog boxes, you’ll be glad to know you can get that control back also. (This applies to Word, Excel, PowerPoint and Publisher).

  1. Back to File tab, Options
  2. Click the Save category at left
  3. Click in the checkbox so that the Backstage does not display
  4. Click OK

Test your Open or Save As commands and you have instant dialog box.

Make this process even faster

Press F12 function key for instant Save As (all Office programs except Publisher)

Use CTRL O for Open (all Windows programs)

If this was helpful to you, please leave a comment!