Diana Angenent
Why should I use MS Excel Tables?
Excel Tables are more than just fancy formatting. The beauty of these tables is not the instant formatting but the power it gives you in being able to analyse the data.
Since Excel 2007, you can use the Table command to convert a set of data into a formatted Excel table. Easily convert lists of data into Excel tables to easily sort, filter and view your data.
Preparing your data
Before you convert the data into a table, the data needs to be organised. Follow these guidelines to ensure your data will convert well.
- The data should be organised into rows and columns, with each row containing information about one record, such as a client, order, or expense item.
- The first row of the list should contain a short, descriptive and unique heading for each column.
- Each column in the list should contain one type of data, such as dates, currency, or text.
- Each row in the list should contain the details for one record only, like as a sales order. If possible, include a unique identifier for each row, such as an order number.
- The list should have no blank rows, and no completely blank columns.
- The list should be separated from any other data on the worksheet by at least one blank row and one blank column between the list and any other data.
When your data has been organised, you can create the formatted table. There are two ways to do this.
Convert a list into a Table
Create a table with the default table styles
- Select a single cell in the list of prepared data.
- From the Ribbon, select the Insert menu and then the Table command in the Tables grouping.
- In the Create Table dialog box, the range of your list should already be populated. Ensure that the My table has headers option is checked and adjust the range if necessary.
- Click OK to accept these settings.
You will see that the list is now automatically displayed in the default table style.
Create a table with a custom table style
- Select a single cell in the list of prepared data.
- From the Ribbon select the Format as Table button from the Home menu and then select the table style you would like to apply.
- In the Format As Table dialog box, ensure that the My table has headers option is checked and adjust the range if necessary.
- Click OK to accept these settings.
You will see that the list is now automatically displayed in the table style you selected. I like to format my tables in different colours for different lists of data and choose a sheet tab colour to match.
Let’s have a look and see what we can do now apart from admiring the pretty colours.
Rename the Table
When it is created, an Excel table is given a default name, such as Table1. I always recommend changing the name to something meaningful, so you can easily identify what the data refers to later when you are constructing formulas using this table.
- Select any cell in the table.
- From the Ribbon, select the Table Tools menu and then the Design tab. Note that the Table Tools menu only appears when a cell in the table is selected.
- At the far left of the menu, in the Properties grouping, click in the Table name box, to select the existing name.
- Type a new name without spaces, such as tblClients, and press the Enter key.
Filtering the Data
Column Filters have been automatically applied. This means you can click the dropdown arrow to the right of the column heading and apply sorting and filtering options. The option I use the most is the Search option. When I need to quickly find a record or group of records relating to a particular client or site, I type a text or number string into the search box. I can then see the related records and choose which ones I’d like to see.
Automatic totals
After you create an Excel table, it's easy to show the total for a column, or for multiple columns, using a built-in Table feature.
To show a total:
- Select any cell in the table.
- From the Ribbon, select the Table Tools menu and then the Design tab. Note that the Table Tools menu only appears when a cell in the table is selected.
- In the Table Style Options grouping, add a check the Total Row option.
- A totals row will be added at the bottom of the table, and one or more column of numbers may show a total.
To change or show other functions:
- Select the totals cell for any particular column.
- Click the dropdown arrow to the right of the selected cell.
- Select any of the listed functions or the More Functions option at the bottom of the list and proceed to add the arguments of your chosen function.
Formula Construction
You can create formulas as you would have in an ordinary list or range but you should notice that rather than a standard cell address reference, the formula references the column with structured references utilising square brackets, an @ sign, and the column name making reading your formulas much easier. For example =[@End Time]-[@Start Time]
The main benefit of a table is that formulas are automatically copied from the column when you add a new record. This helps maintain a high level of data integrity by reducing errors. You can add a new record either by inserting a new row manually or pressing the Tab key when you are in the last cell of the table (the last cell above the totals row).
When utilising totals from your table in dashboard format, you can easily read the formula because it references the table and table column names. For example =tblTasks[[#Totals],[Task Time]]. Don’t worry, if you select the cell/s you want, the names and format of the name is automatically populated, so you don’t have to type the names in.
Automatic list expansion
When you want to add a column of data to the table, all you have to do is type the new column heading in the empty cell to the right of the last column heading and press Enter.
Also read Colour your worksheet tabs
MS Word Tab Stop Tips
If your text look a little disorganised in your Word document, you may want to use Tab Stops to align the text into neat columns. You can use tabs instead of putting text into tables and then hiding the borders. You can set them on the horizontal ruler at the top of the page. (If you can’t see your ruler, go to View > Show grouping > Ruler and tick the checkbox).
There are 5 different tab stops, each with different properties:
- Left – aligns the left edge of the text with the tab stop.
- Centre – centres the text under the tab stop.
- Right – aligns the right edge of the text with the tab stop.
- Decimal – aligns any decimal point under the tab stop.
- Bar –automatically inserts a vertical line under the tab stop.
Set up your tabs using the Tab Alignment button to the far left of your ruler. The button shows the type of tab you will insert. Hover over the tab stop for a second and Word will give you a Tip as to which one it is set. To select a different type of tab to place, click on the Tab Alignment button and it will scroll through the various tabs and indent markers. (We’ll leave the indent markers until another time).
To set a custom tab on the ruler:
- Select the text you want to set the tab stops for.
- Click the Tab Alignment button to select the type of tab you want to use.
- Select the location of your tab stop on the ruler.
There is another button I use frequently when setting and sorting out my tab stops and that is in the Home tab > Paragraph grouping > Show/Hide button to display my paragraph marks. (The one that looks like a reverse P). It show my tab markers and helps me to tidy up messy text.
One final tip on moving tabs is that you can simply drag your tabs to a new location on the ruler. It is important to remember that you need to select the whole paragraph or pieces of text for which you want the tabs to move. The tabbed text moves with the tab stop so you can see how the change will affect your text.
Also read Quickly Apply Formats to Text
MS Word Character & Paragraph Formatting Shortcuts
Word also has loads of keyboard combos for applying character formatting.
Applying Character Formatting
You can use the shortcuts to apply formatting to selected text or to whatever you type next if no text is selected.
- Ctrl+B: Apple bold formatting
- Ctrl+I: Apply italic formatting
- Ctrl+U: Apply underline formatting
- Ctrl+Shift+W: Apply underline formatting to words, but not the spaces between words
- Ctrl+Shift+D: Apply double underline formatting
- Ctrl+D: Open the Font dialog box
- Ctrl+Shift+< or >: Decrease or increase font size one preset size at a time
- Ctrl+[ or ]: Decrease or increase font size one point at a time
- Ctrl+=: Apply subscript formatting
- Ctrl+Shift+Plus key: Apply superscript formatting
- Shift+F3: Cycle through case formats for your text. Available formats are sentence case (capital first letter, everything else lower case), lowercase, uppercase, title case (first letter in each word capitalized), and toggle case (which reverses whatever’s there).
- Ctrl+Shift+A: Formats all letters as uppercase
- Ctrl+Shift+K: Formats all letters as lowercase
- Ctrl+Shift+C: Copies the character formatting of a selection
- Ctrl+Shift+V: Pastes formatting onto selected text
- Ctrl+Space: Removes all manual character formatting from a selection
Applying Paragraph Formatting
And just like with character formatting, Word has a bunch of shortcuts particular to formatting paragraphs.
- Ctrl+M: Increases a paragraph’s indent one level each time you press it
- Ctrl+Shift+M: Reduces a paragraph’s indent one level each time you press it
- Ctrl+T: Increases a hanging indent each time you press it
- Ctrl+Shift+T: Reduces a hanging indent each time you press it
- Ctrl+E: Center a paragraph
- Ctrl+L: Left-align a paragraph
- Ctrl+R: Right-align a paragraph
- Ctrl+J: Justify a paragraph
- Ctrl+1: Set single-spacing
- Ctrl+2: Set double-spacing
- Ctrl+5: Set 1.5 line Spacing
- Ctrl+0: Remove one line spacing preceding a paragraph
- Ctrl+Shift+S: Open a popup window for applying styles
- Ctrl+Shift+N: Apply the normal paragraph style
- Alt+Ctrl+1: Apply the Heading 1 style
- Alt+Ctrl+2: Apply the Heading 2 style
- Alt+Ctrl+3: Apply the Heading 3 style
- Ctrl+Shift+L: Apply the List style
- Ctrl+Q: Remove all paragraph formatting
Also read Word Text Selection Shortcuts
Word General Shortcuts
There are many general program shortcuts in Microsoft Word that make it easier for you to do everything from save your document to undo a mistake.
- Ctrl+N: Create a new document
- Ctrl+O: Open an existing document
- Ctrl+S: Save a document
- F12: Open the Save As dialog box
- Ctrl+W: Close a document
- Ctrl+Z: Undo an action
- Ctrl+Y: Redo an action
- Alt+Ctrl+S: Split a window or remove the split view
- Ctrl+Alt+V: Print Layout View
- Ctrl+Alt+O: Outline View
- Ctrl+Alt+N: Draft View
- Ctrl+F2: Print Preview View
- F1: Open the Help pane
- Alt+Q: Go to the “Tell me what you want to do” box
- F9: Refresh the field codes in the current selection
- Ctrl+F: Search a document
- F7: Run a spelling and grammar check
- Shift+F7: Open the thesaurus. If you have a word selected, Shift+F7 looks up that word in the thesaurus.
Also read Word Text Navigation Shortcuts.
Word Text Navigation Shortcuts
You can use keyboard shortcuts to easily navigate throughout your document. This can save time if you have a long document and don’t want to scroll through the entire thing, or simply want to easily move between words or sentences.
If you think of the CTRL key as a booster key, these shortcuts are easier to remember.
- Left/Right Arrow: Move the insertion point (cursor) one character to the left or right
- Ctrl+Left/Right Arrow: Move one word to the left or right
- Up/Down Arrow: Move up or down one line
- Ctrl+Up/Down Arrow: Move up or down one paragraph
- End: Move to the end of the current line
- Ctrl+End: Move to the end of the document
- Home: Move to the beginning of the current line
- Ctrl+Home: Move to the beginning of the document
- Page Up/Page Down: Move up or down one screen
- Ctrl+Page Up/Page Down: Move to the previous or next browse object (after performing a search)
- Alt+Ctrl+Page Up/Page Down: Move to the top or bottom of the current window
- F5: Open the Find dialog box with the “Go To” tab selected, so you can quickly move to a specific page, section, bookmark, and so on.
- Shift+F5: Cycle through the last three locations where the insertion point was placed. If you just opened a document, Shift+F5 moves you to the last point you were editing before closing the document.
Also read Word Text Selection Shortcuts.
Word Text Selection Shortcuts
Utilise the Shift key to easily select text.
You may have read a previous blog, that the arrow keys are used for moving your insertion point around, and the CTRL key is used to modify that movement. Using the Shift key to modify a lot of those key combos lets you select text in different ways.
- Shift+Left/Right Arrow: Extend your current selection by one character to the left or right
- Ctrl+Shift+Left/Right Arrow: Extend your current selection by one word to the left or right
- Shift+Up/Down Arrow: Extend selection up or down one line
- Ctrl+Shift+Up/Down Arrow: Extend selection to the beginning or end of the paragraph
- Shift+End: Extend selection to the end of the line
- Shift+Home: Extend selection to the beginning of the line
- Ctrl+Shift+Home/End: Extend selection to the beginning or end of the document
- Shift+Page Down/Page Up: Extend selection down or up one screen
- Ctrl+A: Select the entire document
- F8: Enter selection mode. While in this mode, you can use the arrow keys to extend your selection. You can also press F8 up to five times to extend the selection outward. The first press enters selection mode, the second press selects the word next to the insertion point, the third selects the whole sentence, the fourth all the characters in the paragraph, and the fifth the whole document. Pressing Shift+F8 works that same cycle, but backwards. And you can press Esc any time to leave selection mode. It takes a little playing with to get the hang of it, but it’s pretty fun!
- Ctrl+Shift+F8: Selects a column. Once the column is selected, you can use the left and right arrow keys to extend the selection to other columns.
Also read Word Text Navigation Shortcuts.
Word Text Editing Shortcuts
To start utilising shortcuts in Microsoft Applications, try just one to start with and introduce a new one each week. Before long you'll be using so many shortcuts you'll notice a real difference in your productivity.
- Backspace: Delete one character to the left
- Delete: Delete one character to the right
If you delete words or phrases by using the backspace key repeatedly, avoid RSI by trying the following shortcut to delete full words. Over the coming week, every time you need to delete a full word, try using the CTRL key. Just think of the CTRL key as a booster key.
- Ctrl+Backspace: Delete one word to the left
- Ctrl+Delete: Delete one word to the right
Word also provides a number of keyboard shortcuts for editing text.
- Ctrl+C: Copy or graphics to the Clipboard text
- Ctrl+X: Cut selected text or graphics to the Clipboard
- Ctrl+V: Paste the Clipboard contents
The Spike feature is an interesting variant on the regular clipboard. You can keep cutting text to the Spike and Word remembers it all. When you paste the Spikes contents, Word pastes everything you cut, but places each item on its own line.
- Ctrl+F3: Cut selected text to the Spike
- Ctrl+Shift+F3: Paste the Spike contents
Easily copy the header and footers.
- Alt+Shift+R: Copy the header or footer used in the previous section of the document
How to Create an Excel Macro Button
Create a button in Excel to move the user to another worksheet
It is actually fairly easy to create a button on a worksheet that you can click to take you to another sheet in the workbook. It is a fun way to get started with macros in Excel if you have never made one before.
To do this, first check that the Developer tab is visible. If not at the top with your other menus you will need to display it via the File menu. Select Options and Customize Ribbon in the left hand menu and check the Developer option in the right hand pane. Click OK to exit.
Then decide which sheet will contain the button and which sheet you will select when you click, the button. We’ll add a button to Sheet1 to take us to Sheet3.
- So click somewhere in Sheet1 and, from the Developer tab on the Ribbon, choose Record Macro.
- You need to give the macro a name that will identify later as the correct macro so in this case type GoToSheet3 in the Name box. Macro names must be all one word with no spaces.
- From the Store Macro in List choose to Store the macro in This Workbook and click OK.
Recording your macro
Excel is now in 'Record Mode' so only select the Sheet3 sheet tab click the Stop Recording button on the Developer menu.
Now we need to add a button to Sheet1 that will run the macro.
- Return to Sheet1 by selecting the Sheet1 tab and from the Developer menu, select the Insert option and click the Button (Form Control) option at the top of the drop down list. You must choose the Form Controls button and not the Active X Controls button.
- Drag the button onto the worksheet and when the Assign Macro dialog appears, click the GoToSheet3 macro and click OK.
- Select the text on the button and type Click to go to Sheet3.
- Click outside the button to deselect it and test it by clicking on the button to see it at work. When you click it you will be taken automatically to Sheet3.
If you need to make changes to the text on the button right-click on it to get access to the text. You can’t click it to select it because clicking it runs the macro attached to it.
Excel Worksheet Tab Navigation Tips
Earlier this week I was asked a question that I’d like to share with you. The question was to do with Microsoft Excel and navigation in a workbook that has dozens of worksheets.
“Is there an easy way to get to a named tab rather than having to scroll through the many tabs going backwards and forwards?”
If you're already using the arrow buttons to the left of the sheet tabs to skip one by one or to the end and start of the sheets, you may like this quick tip.
- Hold your CTRL + left click on the arrow buttons to the left of the sheet tabs to skip to the last worksheet.
But even better than that, I hope you're going to love my favourite tip to reveal a list of your sheets.
- Right-click on the arrow buttons to the left of the sheet tabs to view a list of the sheets. Double-click to select the sheet.
If you're willing to give macros a try, you could add a button to the main worksheets so you can jump to it in a click. See this tip to walk you through how. How to Create an Excel Macro Button
Check your Excel Formulae for Errors
When using a spread sheet program it’s easy for errors to creep in if you are not vigilant.
At this time of year when spread sheets are being used to generate End of Year results and updating for use in the new Financial Year, it’s important to check your formulae are still doing what you want and expect them to be doing.
There is an easy way to check your formulas rather than having to select each cell, giving you an easy way to scan your formulae for inconsistencies
If you are using Excel, you may not be aware that you can toggle between 'Normal View' and a 'Formula View.
- Switch views by using the CTRL +` (the one under your escape key) to reveal the formulas in your sheet. CTRL + ` to switch back to Normal View.
- Or, in the Formulas tab you can find the Show Formulas button.
Error Checking Tips
Scan your worksheet columns for visual inconsistencies and accidental overtypes.
If you select a cell, the cells utilised in the formula are highlighted.