Data Validation in Excel

Data Validation in Excel is a technique that restricts the type of entry allowed within a cell.  The restriction is based on specific, user-defined criteria and is an excellent way to ensure greater quality control and functionality within spreadsheets.  As an example, creating a Data Validation Drop-Down-List is an excellent way to give you complete quality control when applying filters in Excel.  By default, Excel turns every cell entry into a filter criteria, so if someone types Mechanic, someone else types mechanic and someone else types mechanics, you have three different filter criteria that all mean the same thing.  It is much harder to filter the data without having to change the entries to a consistent format.  If however, you create a Data Validation Drop-Down-List, the end user can only pick from the list and all the entries in the spreadsheet will be consistent, giving you greater control over the filters, once applied.

It is also very useful for giving the end-user specific information and instruction on how a spreadsheet should be filled out as you may include input messages and error alerts.  An input message and an error alert can both be used to give direction and correction when filling out a spreadsheet.  Data Validation is found (across Office 2007 – 2013) on the Data Tab of the Ribbon within the Data Tools Group.  To access the Data Validation feature in Excel:

  • Select the Data Tab
  • Select the Data Tools Group
  • Click the Data Validation Button
  • The Data Validation dialog box will launch
  • Click the Settings Tab to select the type of validation
  • Click the Input Message Tab to enter an input message
  • Click the Error Alert Tab to enter an error message
  • Click the OK button when done

Below is a list of the types of Data Validation that may be applied in Excel:

Any Value Restricts data entry to any value within limits
Whole Number Restricts data entry to a whole number within limits
Decimal Restricts data entry to a decimal number within limits
List Restricts data entry to values in a drop-down List
Date: Restricts data entry to a date within a time frame
Time Restricts data entry to a time within a time frame
Text Length Restricts data entry to text of a specified length
Custom Use a formula to calculate what is allowed

 So next time you are creating a spreadsheet, consider using this timesaving tool to prevent inconsistent entries and save yourself time and re-work in the long-run.

Posted in MS Excel Tips

Using Auto Sum and the Function Library in Excel

The Function Library in Excel and the Auto Sum feature are two of the easiest and fastest ways by which to write formulas in Excel.  The beauty of each feature is that they write the formula for you and all you have to do is select the criteria.  This means that you do not need to have expert knowledge of all the Formula Operators that must be used in order for a formula to calculate correctly.  I highly recommend each of these features as a way to streamline workflow and reduce formula errors.

The Insert Function button and the Auto Sum button are both found on the Formulas Tab (across all versions of Excel from 2007 to 2013).  The Auto Sum feature, which appears on the Ribbon as a backward looking E also appears on the Home Tab.

Using Auto Sum

The default of Auto Sum is to capture the criteria directly above or beside the active cell.  So if you place your cursor in cell A5 and then click the Auto Sum button, Auto Sum will select Cells A1:A4 by default.  If you were to place your cursor in Cell E1 and click the Auto Sum button, Auto Sum would select Cells A1:D1 by default.  You can always click the Auto Sum button and then re-select the criteria range to customize the formula.  Once your formula is in place, simply hit the enter key on your keyboard. 

You will notice that there is a drop down arrow below the Auto Sum button.  This drop down arrow allows you to choose other functions besides the Sum feature.  Average will give you the average of a range of criteria, Count will count the number of entries and Max and Min will give you the largest and smallest numerical values in a range of data.  All of these selections are applied exactly the same way.  Simply select the function you wish to use, make sure your criteria range is correct and hit the enter key on your keyboard.

Using the Function Library

The Function Library contains all the formulas that are available within Excel.  You can access the functions in the Library either by clicking one of the category buttons in the Function Library or by searching for a function using the Insert Function button.  When you choose either one of these methods, the Function Arguments dialog box will launch.  The beauty of using this method is that it supplies all the Function Arguments necessary to write a formula and gives you an explanation of what each Function Argument represents.  All the end-user needs to do is enter the Function Arguments and click the “OK” button when done.  Excel will write the formula for you and include all the Operators necessary for the formula to calculate correctly.

Both the Auto Sum feature and the Function Library are excellent ways to quickly write accurate formulas and I highly recommend them as a Best Practice to my clients.

 

 

Posted in MS Excel Tips

Creating a Custom List in Excel

Custom Lists in Excel are very similar to the Auto Fill feature.  In fact, a Custom List is actually an Auto Fill that is based on specific, user-defined criteria.  An example would be a list of employees.  If you have a list of employees that you use repeatedly and you do not want to copy that list from another spreadsheet every time you use it, your Best Practice would be to create a Custom List.  Then the next time you want to use that list, you simply type the first name in the list and Auto Fill the list to its completion.

There are two ways to create a Custom List in Excel, either by typing the list from scratch or by importing a list already existing within Excel.  I will cover them both in this post, although accessing the feature differs slightly depending on which version of Office you are using.

Accessing Custom Lists from Excel 2007

  • Click the Office button
  • Select Excel Options
  • Select the Popular Tab
  • Click the Edit Custom List button

Accessing Custom Lists from Excel 2010 and Excel 2013

  • Select the File Tab
  • Select Excel Options (2010) or Options (2013)
  • Select the Advanced Tab
  • Scroll to the bottom of the Advanced Tab
  • Under the General Heading, click the Edit Custom List button

Typing a List from Scratch

  • Access the Custom Lists dialog box
  • Place your cursor in the List Entries section of the dialog box
  • Type your List
  • Click the Add button
  • Click the “OK” button when done

Importing an Existing List

  • Access the Custom List dialog box
  • Place your cursor in the “Import List from Cells” section of the dialog box
  • Select the range of data in Excel that you wish to Import
  • Click the “Import” button
  • You should see your list appear under the “Custom Lists” section of the dialog box
  • Click “OK” when done

Editing a Custom List

  • Access the Custom List dialog box
  • Select the List under the “Custom Lists” section of the dialog box
  • Make the necessary changes in the “List Entries” section of the dialog box
  • Click the “OK” button when done

Deleting a Custom List

  • Access the Custom List dialog box
  • Select the List under the “Custom Lists” section of the dialog box
  • Click the “Delete” button
  • Click “OK” when done
Posted in MS Excel Tips

Using Auto Fill in Excel

The Auto Fill feature in Excel is a powerful tool for streamlining workflow and is an excellent way to quickly fill series of data and copy formulas.  For instance, Auto Fill has been programmed to know that Tuesday comes after Monday and that February comes after January.  So instead of typing out a series of days or months, you can just type the first day or the first month and then fill the series by dragging the Fill Handle across the empty cells you wish to populate.

The Fill Handle is located in the bottom, right hand corner of the active cell or range (the selected cell or range) and appears as a small, solid black box.  To activate the Auto Fill feature:

  • hold your cursor over the Fill Handle until a solid, black cross appears (make sure you have the black, solid cross and not the cross with arrows on each end)
  • hold your cursor over the solid black cross and click with your left mouse
  • keep the left mouse held down and drag across the range of cells you wish to populate
  • let go of the left mouse when done

Creating Numerical Patterns using Auto Fill

When filling a numerical series you must create a numerical pattern first for Auto Fill to work properly.  As an example, using Auto Fill will not automatically give you the number 2 after the number 1.  If you type 1 in a cell and try to Auto Fill that cell down, you will get a 1 all the way down the list.  You must type a 1 in the first cell and type a 2 in the second cell.  Then highlight both cells together and Auto Fill from the second cell (the 2).  Auto Fill will then continue with the pattern you have created (i.e., 3,4,5,6).  Similarly you may create other Auto Fill patterns such as 2,4,6,8 or 4,8,12,16.  Auto Fill will recognize the pattern you create and fill the series accordingly.

Copying Formulas with Auto Fill

As well as filling series of data, Auto Fill is a very efficient way to copy formulas in Excel.  By default each cell reference in Excel is a “relative” reference.  That means, as you Auto Fill formulas, the cell reference changes relative to its new position in the worksheet (i.e., =SUM(A1:A10) becomes =SUM(B1:B10) and then =SUM(C1:C20)) as you fill the formula across columns.  If you Auto Fill a formula down by row, then =SUM(A1:D1) becomes =SUM(A2:D2) and so on.

Using Auto Fill to duplicate formulas is faster and more efficient than using the copy feature and I highly recommend this as a Best Practice. 

Posted in MS Excel Tips

Customizing the Quick Access Toolbar and Ribbon in Excel

Ok, lets talk about customizing the Quick Access Toolbar and the Ribbon.  These two features are one of the best ways to streamline workflow in excel and I highly recommend them as a Best Practice.  I also recommend placing the Quick Access Toolbar below the Ribbon as a Best Practice.  Placing the Quick Access Toolbar below the Ribbon gives you more room on the Toolbar (it will not run into the Title Bar at the top of the operating window) and makes it slightly easier to read.  To show the Quick Access Toolbar below the Ribbon, simply right-click the Toolbar and select “Show Quick Access Toolbar Below the Ribbon”.

The purpose of customizing the Quick Access Toolbar is that you can load the toolbar up with the commands you commonly use, saving yourself time and energy from having to switch from Tab to Tab to find the commands you need.  You may add individual commands to the Quick Access Toolbar or you may add entire Groups to the Quick Access Toolbar.

The easiest way to add a command to the Quick Access Toolbar is to right-click it’s button on the Ribbon and select “Add to the Quick Access Toolbar”.  To add a Group to the Quick Access Toolbar, hover over the name of the Group with your mouse, right-click and choose “Add to Quick Access Toolbar”.  Similarly you may remove a command or group the same way.  Simply right-click the command or Group on the Quick Access Toolbar and choose “Remove from Quick Access Toolbar”.

The option to customize the Quick Access Toolbar is available in Office 2007, Office 2010 and Office 2013.

Customizing the Ribbon (only available in Office 2010 and Office 2013) allows you to create your own Tabs and Groups in Excel and be even more specific in how you organize commands on the Ribbon.  By creating your own Tabs and Groups, you can combine all your most commonly used commands together and have them right at your fingertips.

To Create and Name a New Tab

  • Right-click the Ribbon
  • Select Customize the Ribbon
  • The Customize Ribbon dialog box will launch
  • Click “New Tab” from the bottom, right-hand corner of the dialog box
  • A new Tab will be created and by default, one new Group will be created as well
  • Make sure the New Tab is selected and click Rename from the bottom, right-hand corner of the dialog box
  • Rename the Tab
  • Click OK when done

To Create a New Group

By default, when you create a New Tab, Excel will create one New Group as well.  To add additional Groups to the Tab you have created:

  • Ensure the Tab you have created is selected
  • Click New Group from the bottom, right-hand corner of the dialog box
  • A New Group will appear under the Tab you have created

To Re-Name a New Group

  • Select the Group you have created
  • Click Rename from the bottom, right-hand corner of the dialog box
  • Rename the Group.  You can ignore all the symbols above, you don’t have to use them if you don’t want to.
  • Click OK when done

To Populate a New Group

  • Ensure the New Group you have created is selected
  • Select a command from the category listing on the left hand side of the dialog box
  • Click the Add button in the middle of the dialog box
  • Repeat the process until you have all the commands added to the Group
  • Click OK when done

 

Posted in MS Excel Tips

Excel Short-cut Keys and Key Tips

Short-cut keys are a great way to streamline workflow in Excel.  One of the benefits of using short-cut keys is that they keep your hands on the keyboard and free of the mouse, which can help increase productivity and alleviate repetitive strain.  Below is a list of commonly used short-cut keys.  Many of these short-cut keys are standard right across Microsoft Office but a few, such as F4 (to apply an absolute reference) and Alt+Enter (to insert a hard return) are specific to Excel. 

Standard Microsoft Office Short-cut Keys

  • Control A – Select All
  • Control B – Bold
  • Control C – Copy
  • Control F – Find and Replace
  • Control I – Italics
  • Control O – Open
  • Control S – Save
  • Control U – Underline
  • Control V – Paste
  • Control W – Close
  • Control X – Cut
  • Control Y – Redo
  • Control Z – Undo
  • F7 – Spellcheck

Excel Short-cut Keys 

  • Control N – New Workbook
  • Alt+Enter – Inserts a hard return within a cell
  • Alt+=(equal sign) – Insert an AutoSum Formula
  • Shift+Alt+F1 – Insert a new worksheet
  • Shift+Control+4 – Applies the Currency format
  • Control+F9 – Minimizes the active window
  • Shift+F3 – Display the Insert Function dialog box
  • ALT+F2 – Display the Save As dialog Box
  • F4 – Will turn a relative reference into an absolute reference by inserting the $ symbol before each cell reference. (You must highlight the reference in the Formula Bar first).
  • F1 – Help

Key Tips

Another way to work off the keyboard and avoid using the mouse is to activate the Key Tips available in Excel.  Simply hold the ALT key down for a few seconds and see the Key Tips appear.  Once they appear, type one of the letters or numbers to access its corresponding application.  Key Tips will automatically turn off if not used after a few moments or you may press the Escape Key to turn them off.  If you wish to turn them back on, simply press the ALT key again.  Alternatively, you may press F10 to turn the Key Tips on and off.

Posted in MS Excel Tips

Excel Navigation and Selection Techniques

Ok, let’s start with the basics.  By using some simple navigation and selection techniques available in Excel, you can move around a spreadsheet with greater ease and get work completed more efficiently.

Navigation Techniques

  • Tab  -  Moves you one cell to the right
  • Shift+Tab  -  Moves you one cell to the left
  • Home  -  Takes you to Column A of the current row
  • Page Up/Page Down  -  Contrary to the names, Page Up and Page Down actually take you up/down one screen at a time.
  • Alt+Page Up  -  Moves you one screen to the left
  • Alt+Page Down  -  Moves you one screen to the right
  • Control+End  -  Takes you to the last row, last column in a range of data
  • Control+Home  -  Takes you to beginning of a spreadsheet
  • The Name Box  -  The Name Box, found to the left of the Formula Bar, is a great way to navigate in larger spreadsheets.  Name a range of data and the next time you want to go there, simply select the named range from the name box.  You can also use named ranges in formulas to streamline workflow.

Did you know?

The default in Excel is to move to the next cell down in a column when you press the enter key.  If you are working in a large, horizontal spreadsheet and wish to move to the right, you can change the default through the Excel Options, Advanced Tab (under editing options).  Just remember that you will have to change the default back once you are finished with that spreadsheet.

Selection Techniques

  • Single Click  -  Select a single cell
  • Double Click  -  Select the contents of a cell.  You may also select the contents of a cell in the formula bar.
  • Shift+Click  -  Selects a contiguous (adjacent) range of cells.  Place your cursor at the first insertion point, then hold down the shift key and keep it held down, click at the last insertion point and then let go of the shift key.  This is a great way to select large ranges of data that are adjacent to each other without having to drag your mouse through them to make a selection.
  • Control+Click  -  Selects a non-contiguous (non-adjacent) range of cells.  Select your first cell, hold the control key down and keep it held down and click to select the rest of the cells.  Then let go of the control key.  This is a great way to select numerous cells at one time and can really speed things up when formatting in Excel.  Simply select all your cells at one time and make the formatting change only once.
  • Control+A  -  Select an entire worksheet.
  • Shift+Control+Home  -  Selects an entire range of data above and to the left of the active cell (great for quickly selecting a print area or deleting content).
  • Shift+Control+End  -  Selects an entire range of data below and to the right of the active cell (great for quickly selecting a print area or deleting content).
  • The Name Box – You can select ranges of data in Excel by typing the range within the Name Box.  As an example, type A1:A20 in the Name Box and Excel will select the range A1:A20 for you.

 

Posted in MS Excel Tips

Top Streamliners and Timesavers

Busy, Busy, Busy.  These days, life is busy and particularly, work life can be very busy.  Most of us are juggling multiple tasks and time sensitive deadlines on a regular basis.  Who has time to waste spending more energy on any given task than is necessary?  Not me, I’ve got better things to do with my time!  I have literally been in situations where the courier is waiting at the door to take a competitive proposal and the clock is ticking.  Miss the deadline, miss the opportunity for the work.  Can you spell stress?  Honey, I know your pain.

To that end, I am starting a new series on the top streamliners and timesavers in Excel, Word, PowerPoint and Outlook.  We’ll start with the basics and then move on to the features that can really simplify workflow and make your working life a lot easier.  Although not an exhaustive list, these features of the Microsoft Office program go a long way to streamlining workflow and increasing productivity.  In the first part of this series we’ll look at the benefits of using the following Excel features:

  1. Navigation and Selection Techniques
  2. Shortcut Keys & Key Tips
  3. Customizing the Quick Access Toolbar and Ribbon
  4. Using AutoFill
  5. Creating Custom Lists
  6. Using the Function Library and AutoSum
  7. Applying Data Validation
  8. Creating Pivot Tables
  9. Applying Conditional Formatting
  10. Consolidating data across spreadsheets

In each of these topics I’ll give a brief explanation of the feature including the best uses and applications for it and the benefit of using it.  So stay tuned and increase your knowledge and power when working in Excel!

Posted in MS Office Tips
"Hawthorn Training delivers expert and up to date software training solutions for your business."