Category Archives: Excel

Use Excel Sparklines Instead of Charts to Display Data Trends

Excel, Office, , , , ,

What are Sparklines?

Sparklines were introduced in Microsoft Excel 2010 and add a quick way to display results without having to insert an entire chart object. A sparkline displays a visual representation of data as a tiny chart inside a single cell, and can be used to show trends in a series of values, such as sales for a company, products, sales representatives, time frames, economic cycles, or pretty much anything that has discernible increases or decreases.

Is a Sparkline a Chart Object?

The tiny sparkline chart actually resides in the background of a cell and displays a separate result in each cell for the range selected, unlike the chart object that displays all results in a single chart, such as a column, bar, pie, or a myriad of other chart types.

There are three different sparkline chart types, found on the Insert tab:

Sparklines Group

Create a Sparkline Chart

Because sparklines live in the background of a cell, you can insert them anywhere on your worksheet. However, people wouldn’t have a clue what they were supposed to represent as the Formula Bar is blank for any cell that contains a sparkline, unless you enter something else in the cell. Ergo…Best to put them next to your data.

Sparklines are automatically grouped by default but you can ungroup them to treat the cells separately. When you insert the chart, you can click in the first cell where you want the chart, and then select the rest of the range when inside the dialog box, or easier method, select the range first, and it will be auto displayed in the box:

Range to insert Sparklines

Click Insert tab to display the dialog box. If you have selected all the cell references for results, the Location Range is filled in and the Data Range: box is waiting for you to select the value cells:

Create Sparklines dialog bocx

Select the full range with the mouse of the values you are charting (in this case, B4:E8):

Data range for Sparklines

Click OK to insert the mini-charts.

Line Sparklines inserted next to data

Note: If you only selected the one cell, F4, when you inserted the sparkline, you can use the Fill Handle to populate the rest of desired cells. (You must drag the handle down though as the double click shortcut will not work here).

The Design Tab for Sparkline Tools

You don’t have all the fancy trappings of actual Excel chart objects but, hey, these are pretty spiffy and sometimes can even get the picture across in a clearer manner. The Sparkline Tools tab gets added to the end of the Ribbon and gives you many options for editing and formatting those little gems through the Design tab.

Sparklines Tools Design tab on Ribbon

You probably noticed that no markers displayed on the line charts but you can easily add them. (They are available only on this chart type). In the Show group, just click in the Markers box, and there you go – a marker for each change in value. Now you can use the Style group to choose a quick color change or individually change the Sparkline Color or Marker Color. (See below). Individual markers are also available for any chart type to represent High, Low, Negative, etc., in the Show group.

You can apply a color scheme to your sparklines by choosing a built-in format from the Style gallery or change your chart type from the Design tab (available when you select a cell that contains a sparkline). You can use the Sparkline Color or Marker Color commands to choose a color for the high, low, first, and last values (such as green for high, and orange for low).

Whatever selections you make are applied to all the sparkline cells as they are grouped by default. If you want to personalize a particular cell, or all cells individually, they can be ungrouped, and you can also type a comment directly in the cell without deleting the chart.

Ungroup Sparklines

Ungroup all the sparklines by selecting  that range, and choosing Ungroup from the Group group. (I’m not stuttering, honest)! If you just want to personalize one cell, select it, and choose Ungroup.

Sparkline cell with text entry

Using Sparklines for Stock Performance

This image shows a column sparkline in cell F2 and a line sparkline in F3. Both of these sparklines get their data from cells A2 through E2 and display a chart inside a cell that shows the performance of a stock. The charts show the values by quarter, highlight the high value (3/31) and the low value (12/31), show all the data points, and show the downward trend for the year. The high value marker is green, and the low value marker is orange. All other markers are shown in black.

Stock Performance Sparklines

Cell F6 shows the 5-year performance for the same stock, but displays a Win/Loss chart that shows only whether the year had a gain or a loss. This sparkline uses values from cells A6 through E6.

Sparkline Benefits

Sparklines can be inserted next to the data and take up such little space. They also easily point out a pattern. Any edits to your data automatically update the chart so the changes to trends are instantly represented. They can be inserted for rows or columns of data, and type changed with a mouse click.

*Stock Performance image from Microsoft

Find Sparklines fascinating? What would you use them for? Leave a comment below!

If you like different graphical ways of displaying your data, check out my blogs on using the Camera tool in Excel:

Part 1… https://gaylelarson.com/use-excel-camera-tool-combine-objects-several-workbooks/

Part 2…https://gaylelarson.com/excel-camera-tool-part-2/

Copy and Paste Filtered Subtotals or Visible Cells Only in Excel

Excel, Office, , , , , , ,

Copy and Paste Visible Cells Only (filtered data)

You have used one of several methods to hide some rows for filtered data, or created a table which auto applies filter icons for each column. Now you want to copy and paste just the visible data but discovered to your horror when you pasted to another location, it included the hidden rows!

Excel, Paste only Filtered Data

By default, Excel copies hidden or filtered cells in addition to visible cells. If you want only visible rows, here’s the steps:

  • Select the cell range that you want to copy.
  • Click Home tab, Find & Select in the Editing group and choose Go To Special…
  • Click Special… button in the dialog box.
  • Click Visible cells only radio button and click OK.

Excel, Copy and Paste Filtered Cells Only

  • Click Copy in Clipboard group on Home tab (or press CTRL+C).
  • Click the upper-left cell of the desired paste area and click Paste (or press CTRL+V).

You have now achieved Nirvana!

Tip: You can also use the F5 Function key at Step 2 to bring up the Go To… dialog box and click the Special… button to get the same results.

Add the Icon for Select Visible Cells to the Quick Access Toolbar

Make this great solution even easier and faster by utilizing the Quick Access Toolbar (QAT):

  • Right click anywhere in the Ribbon and choose Customize Quick Access Toolbar… OR click the QAT dropdown arrow, and choose More Commands. 
  • From the Choose Commands From dropdown, choose All Commands.
  • Scroll down and click Select Visible Cells.
  • Click Add and then click OK to add to end of the QAT.
  • If desired, use the arrow boxes to change the icon’s position on the toolbar.

Now all you do is select the range, click the Select Visible Cells icon on the QAT, Copy and Paste – One and done!

Copy and Paste Only Subtotaled Rows

You’ve used the SUBTOTAL function to sum only filtered data and now want to copy and paste to another location. You assume the paste will include the visible subtotaled rows only – Surprise – not! You still need to use the Go To dialog box to accomplish this but if this is something you do often, apply shortcuts:

  • Select the range you want to copy. (Excel is actually selecting the hidden rows as well but this will get taken care of in the next steps).
  • Press F5 function key to display Go To dialog box.

Excel, Copy and Paste only Subtotaled, filtered Rows with Special...

  • Click the Special… button at the bottom of the dialog box.
  • Click Visible Cells Only to select only the visible cells in the selected range.

  • Click OK (or just hit ENTER key as OK is already selected).
  • Press CTRL C to copy the selected visible cells to the Clipboard.
  • Select a destination cell (can be on the same sheet, a different sheet, or on a new workbook).
  • Paste the range by pressing CTRL V. Excel copies only the subtotaled rows.

Now you can copy and paste only those cells or ranges YOU want.

Hope these tips have removed a little head-scratching from one of those features that we want to use on a regular basis but doesn’t always behave the way we expect!

Display Results of Formula Inside Cell During Creation

Excel, Office, , , ,

Would you like to see the values displayed when you are entering a formula but a little fuzzy on how to make that happen?

Get clarity displaying formula values in cell

There are times when you may want to examine the results of a complicated formula from others or as you are creating one inside the cell. This can save time and frustration and avoid the dreaded error message after you hit ENTER or TAB.

If you want to look at the results of a particular part of a formula as you type, or after you or someone else has entered it, use a couple of function keys to complete the process.

Display Formula Values During Formula Creation

Here is a simple example of how handy this can be where we are adding sales of only two of the products, adding fees and dividing by cost, with the formula being entered in H9:

=(B9+E9)*G9/F9

Example displaying formula values in the cell

If I want to know the value of G9/F9 before completing the formula, I can do this directly in the cell.

  1. In the results cell (in this case, H9), type the formula.
  2. Select the part of the formula whose value you want to see. Here, it would be G9/F9.
  3. Press F9. Excel replaces that part of the formula with its result.

Selected portion of formula displays values

If this is the correct result, press ENTER or TAB, and Excel completes the formula and moves to requested cell. You could also press ESC to return to your formula and stay in the cell. Be careful here. If you are creating, not editing a previous entry, ESC will revert to whatever the cell contents were prior to your entry (such as a blank cell).

Display Formula Values Editing Existing Formula

If the formula already exists and you want to display the value, the steps are the same except you need to be in Edit mode in the cell:

  1. Click in the cell and press F2 to edit the formula in the cell. *
  2. Select the part of the formula whose value you want to see (G9/F9).
  3. Press F9. Excel replaces that part of the formula with its result.

*Note: Since the content already exists, you could also double click in the cell to edit. Only difference is that F2 places your insert marker at the end of the contents, and wherever you double click, the insert displays at that point.

Use the Ribbon to Evaluate Formula

There is another way to evaluate a formula by each cell reference – Select the Formulas tab, Formula Auditing group and click on Evaluate formula icon to bring up the Evaluate dialog box.

Example of Evaluate Formula dialog box

Click Evaluate button to see formula results as you move through each cell reference. This is a great way to walk through a formula you are not too familiar with and/or someone else created and has you scratching your head.

Now you can know what is going on in your worksheet before you press Print or Send!

Let me know if you have used these features or how they can help you in the future…

The Excel Camera Tool, Part 2

Excel, Office, , , , , , ,

Create a Dashboard Using Camera Tool

We saw in Part 1 how to add the Camera tool to the Quick Access toolbar and how to use it for capturing linked pictures that automatically update when the source data is changed.

Another great use for the Camera tool is to create dashboards. The pasted objects can be sized proportionately and positioned anywhere on a worksheet. Here’s an example of sales figures and a chart:

Dashboard Example

This can contain as many objects as you choose. Note that the location of the original data is displayed in the Formula Bar.

Arranging as images on a worksheet in a dashboard-like mode is also a great fix for printing all your related information on one page. If you had selected all your original data separately, even from the same worksheet, the Add to Print command would have to be used which would automatically print each addition on a separate page. This way, you can add images, move, size and print at will.

The Camera tool captures everything as an image, including values, color formatting, shapes, even the gridlines. Because it is an image, the Picture Tools | Format tab is displayed on the ribbon so anything you can do to a picture, you can do here, including sizing and rotating. Use the right click menu to quickly perform commands such as Crop:

Moving Original Data

The Formula bar displays the path of the original data and the cell references are absolute by default. This is terrific because if the original data gets moved to another area, your pasted pictures reflect that, and continue to display any updates. Should be automatic but be sure that the workbook name (if different) and worksheet name display as well as cell references. If your workbook and worksheet are named, could look like this:

The Paste Special Option

The Paste Special Linked Picture option is available in Excel versions 2007 and above. If you prefer Paste Special, you can access it from the drop down arrow on the Paste icon in the Clipboard group or by right-clicking over desired destination cell and mousing over the Paste icons:

Paste Special Linked Picture

Tip: If you like keyboard shortcuts, press: ALT-H-V-I to paste a linked image.

Paste Special Linked Picture and Camera Tool Restrictions

Not too many downsides to using the Camera tool but here’s some cautions:

  • Some users say that it does not work with Tables; that the data must be converted to a range, but I have not experienced that with newer versions. The table copies, pastes and updates.
  • If using formulas such as IF function with Camera tool, you must use Named Ranges, rather than cell references to maintain the update connection.

Note: The Copy command in the Clipboard group on the Home tab, contains a Copy as Picture… option. This is handy but know that it pastes an image but not a link. Use when you just want a snapshot of your data.

Grab the post for Part 1 here: Camera Tool Part 1

See – don’t even have to be a photographer! Take some pictures and play with dashboards. What did you create?

Use the Excel Camera Tool to Combine Objects from Several Workbooks

Excel, Office, , , , , ,

Uses for the Camera Tool

There is a little known spiffy tool that has been available for a long time in Excel which allows you to take screenshots of data from multiple worksheets or workbooks and paste them in a separate workbook as objects with links to their original locations. This can include ranges, tables or charts. Even better, if the original object updates, so does the linked object on your “collector” worksheet.

Here’s a short video to give you a quick overview of what the Excel Camera tool can do for capturing data and objects from different areas:

Collect desired data on one worksheet

For instance, you want to know the sales or prices from workbooks saved in different locations. You have figures for sales reps and a corresponding chart in Workbook A, and expenses that you want to track in Workbook B. Someone else may be updating the data but because anything you copy and paste with the Camera tool is pasted as a linked picture, any changes made to original data will auto update your screenshots.

Print collected objects on one page

This is also a great way to collect different areas of the same or separate worksheets or workbooks for printing a variety of data on one page as you can resize and move the different objects anywhere on the worksheet.

Add Camera Tool to Quick Access Toolbar (QAT)

First things first…The Camera is not available on the Ribbon by default so needs to be added to the QAT with the Customize Quick Access toolbar command:

  • Click the drop down arrow at end of Quick Access Toolbar. Choose More Commands… (or right click on the Ribbon)
  • Choose All Commands from drop down arrow next to Choose Commands from:
  • Scroll down the alphabetical list and click Camera.
  • Click Add button to add to Quick Access toolbar.
  • Click OK button at bottom of dialog box to place the Camera icon at the end of the QAT.

How to Capture a Screenshot

Select a range, table or chart to activate the Camera tool. Note, if selecting a chart, select the cell above the top, left border of the chart and draw around it. When you release the mouse, the “marching ants” will be around the object as if you had used the Copy command:

  • Go to your destination; usually in another worksheet or workbook.
  • Click in the desired cell location and the linked picture will auto insert.
  • Move and size the object(s) as desired.

Similar Feature with Paste Special Linked Picture

The newer versions of Excel (2007+) have another feature which behaves the same as the Camera tool – the Paste Special Linked Picture. I still prefer the Camera tool as just clicking on the desired destination cell pastes the linked image, all ready for sizing and relocating but both work.

The Camera tool can also be used to create Dashboards. We’ll cover that and some of the other options in an upcoming blog.

Take a picture and let me know what you think!

Want more ways to use the Camera? See Part 2: Camera Tool Part 2

Conditional Formatting for Clarity and Visual Impact in Excel

Excel, Office, , , , , , ,

Light Up the Cells with Conditional Formatting!

Conditional Formatting is a great tool for instant, visual results based on values, text or formulas in one or more cells. This is accomplished by creating rules for each desired result. It can be as simple as formatting all cells based on their values (the default), which could be applying a color in cell(s) that are above or below a certain value, contain specific text or fall within certain dates as well answer more complex questions.

Conditional Formatting is located on the Home tab | Styles group:

You can also choose to have the results displayed as Data Bars, Color Scales or Icon Sets (arrows or star ratings) instead of one solid color.

More than one condition can be applied to the same range of cells. This example below is returning two different results with two different cell colors based on two different questions (AND requires both conditions to be true but OR allows for either condition to be true to apply the rule).

The formulas in the last two columns have returned Yes or No based on True or False results. Range Names have been created from the header row text to make for easy identification of the cell references in the formulas. Then Conditional Formatting rules have been applied to designate the color(s) based on that answer.

=IF(AND(Years_Under_Contract<2,Number_of_Books_in_Print>4)=TRUE,”Yes”,”No”)

=IF(OR(Years_Under_Contract>5,Number_of_Books_in_Print>=10)=TRUE,”Yes”,”No”)

It is now very clear to see how many authors met none, one or both of the conditions.

Formatting Rules

There are four formatting rules applied here:

Rule Types

This is the Rule Type applied to the first rule for formatting only cells that contain “No” in the last two columns with Pink fill:


Each of the other three rules have their own Rule Description but all are based on Format only cells that contain (the second rule).

Note: You can include a formula directly in the condition by choosing the Rule Type: Use a formula to determine which cells to format and typing the formula in the Edit the Rule Description area.

Have you been using Conditional Formatting and, if so, which rule do you use the most? If not, I hope this post will encourage you to use this powerful feature. Thanks for reading!

Flash Fill in Excel 2013 and Above

Excel, Office, , , ,

Why You Need Flash Fill…

Flash Fill is a time-saving feature that reads patterns in adjacent columns and Lightening Flash
automatically fills the remaining cells in a column based on those patterns. It is useful when you need to join cells (Excel calls this Concatenate), or separate information in cells without wanting to write a cumbersome formula. You might already know the Text to Columns feature but this is faster and easier – Kind of like AutoFill on steroids!

Flash Fill should automatically be on by default but you can check here: File tab > Options> Advanced:


This amazing tool will make short work of many repetitive tasks, all without formulas (or the Text to Columns feature)! Just make sure you are working next to the column(s) you want to Flash Fill. Here’s just a few of the things it can do:

Splitting Input Data

You received a huge spreadsheet where someone has put first and last names in one column, and you need them separated for sorting and filtering. (This could be any data, i.e., department name and phone extension, or salesperson and monthly sales figure. Doesn’t matter as long as the data has a separator, such as a space). Flash Fill to the rescue…

Example: Full names are in Column A. Here’s the steps:

Insert:  two columns to right of Column A, and enter appropriate column header labels.

Type:  “Fred”, in appropriate cell ( B2 in example).

Press:  ENTER to go to cell below.

Type:  “Mary” (note that Excel has figured out what you are doing; displays the remainder of first names, and is awaiting your approval).

Press:  ENTER, and presto, all the rest of the first names are added!

Repeat the above steps in the cells for Last name (In example, C2)

List is now complete:

Just Want Last Names?

You can have flash fill complete only the last name, i.e., just insert one column, and instead of typing “Fred”, you would type “Frump”, ENTER, then “Lamb”, ENTER, and you would be done.

Combining Data

You can do the reverse of splitting data when you have or receive a workbook with data that is in two columns and you would like combined into one.

Example: Fred is in Column A and Frump in Column B:

  1. Insert a blank column to right of Column B.
  2. Type the first instance the way you would like it displayed, i.e., Fred Frump or Frump, Fred.
  3. Press ENTER and start to type the second example. Excel should display the preview for the balance of the column. Just press ENTER to complete.

You Mean Flash Fill Does More?

Give Excel one or two examples of what you are trying to accomplish so Flash Fill can see a pattern. It will work with text, email addresses, dates, numbers and time:

If Flash Fill does not appear to work when entering the second example, you can activate it from the Ribbon: Data  tab > Flash Fill or keyboard with CTRL E. Just click in the second cell (ensure blank) and use the Ribbon command to fill down the column.

ALERT: Be aware that if you change source data, cells containing flash fill data will not update as there are no formulas involved.

There are many other ways to use Flash Fill. Now that you can save all that time entering data, what ways will you use it?

Reasons to Format Excel Spreadsheets with Cell Styles

Excel, Office, , , , , , ,

Use Cell Styles to Format in Excel to save time and frustration and look smart doing it!

Here’s why you want to use Cell Styles to format Excel data:

  • Apply professional formatting to a worksheet in a flash.
  • Consistency across worksheets.
  • Built-in styles are labelled according to purpose for easy use.
  • Styles are customizable so you can edit existing styles or create your own to match your needs.
  • One click formatting.

Have you ever wondered how to format a spreadsheet in a hurry? Here’s a simple example of a worksheet with boring, unformatted data:

Name Region Q1 Q2 Q3 Q4 Total
Smith South 21223 17855 24855 21377 85310
Jones Southwest 23456 29550 24294 25335 102635
Wing North 19954 22600 19448 26450 88452
Baker South 17564 25439 32944 24000 99947
Folsom North 28543 28540 24400 28550 110033
Range Northwest 19534 30558 21844 19605 91541
McKnight West 20585 29667 27595 13605 91452
Crump South 24957 31322 15330 27550 99159
Rogers North 30332 27407 26440 24007 108186
Morris East 25395 27700 16500 27500 97095
Rollins South 35822 31854 24384 31005 123065
Jefferson Southwest 20949 23100 21774 27550 93373

But now you get a call to send it to a co-worker or the boss or it has to be ready for a presentation. Using individual formatting tools, it can take longer to format than it took to create it!

Format the Column headers and Data Content

Want to look smart really fast? Here’s the fix…First, I want to format the header row:

Select the header row in the worksheet

On the Home tab, in the Styles group, click Cell Styles icon:

If you have a larger screen and/or higher resolution, you may see several cell styles already displayed. See all styles by clicking the More arrow button at the bottom of the scroll bar for the group.

This box below will display with lots of options that are labelled for specific uses but you can use them for any purpose you choose. Roll your mouse over the sections and because they are live preview, you can see the results before you actually choose the option. I want Heading 1 for my header row, so I point and click to apply it.

I’ve selected Heading 1 under the Titles and Headings section:

Next select the body of the spreadsheet:

Click Cell Styles again, and choose whatever style you think would look best for your data. (If you are using Input Style, don’t include the Total column as you’ll probably want to use the Calculate Style for that to indicate formulas).

Total Row Style

If you have a total row, quickly make your totals stand out with the Total Style:

Data and Model Section

A way to alert users about which data they can edit is to use the Input and Calculation styles under the Data and Model section. It can’t stop them from overwriting formulas, etc., but it does signify the ranges that they should not edit. If you don’t like the default colors, you can change them. (See below)

Remove Styles

So, you’ve been playing and got carried away and now your data looks like a kindergarten project! Don’t worry if you goof and want to remove a style. Just select that range of cells; go back to the Cell Styles icon to the Style group and click on Normal to reset the cells to your default font style and size. (Located in the upper left of the first section, Good, Bad
and
Neutral).

Customize/Create Styles for Text

You can modify any style or leave the original styles and create a duplicate for your new style. I would recommend the latter. Here’s how to make changes in font, color, etc. for any style:

Example here is for Heading 1:

  • Click the Cell Styles icon (Home tab, Styles group).
  • Right click over the style you want to change.
  • Choose Duplicate (or Modify if you want to edit the original style) to display the Style box:

  • Change the name.
  • Click the Format… button and make desired changes. (Ensure Font tab is selected).
  • Click OK, OK.

Changes to Numerical Data Styles

If you want to change the Input or Calculation or any other numerical styles to add number formatting as well, the process is the same except you:

  • Right click over the style name.
  • Choose Duplicate.
  • Type in a new style name.
  • Click on the Number tab in the Format Cells dialog box.
  • Click on Currency in the Category pane.
  • Choose if you want the style to always display 2 decimal places or change to 0 if you want that for the style.
  • Make any other desired changes to Font, Alignment, etc., using the tabs at top.
  • Click OK, OK.

Apply Customized Styles

This can be a little confusing after modifying a style as it looks like nothing happened. The new style is not applied to your selected data. because you have created the style but not yet applied it.

When you customize or create new styles, Excel adds a Custom section to the top of the Cell Styles list. Just select desired data, click Cell Styles and click on your customized style. I added Currency formatting to the Input Style but left the background cell color; applied Currency formatting for the Calculate Style but changed to red color so it would be obvious not to enter data in those cells:

I selected the quarterly data to apply my customized Input Style, and then selected the Total column to apply my new Calculation Style. If there is also a Totals row, I would select them both first, and then go to Cell Styles and click my customized style:

Create your own styles and save a bundle of time while looking darn professional as well!

Let me know how it works for you…

AutoFill with a Custom List in Excel

Excel, Office, , , , , ,

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

Excel, Office, , , , , ,

 

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!