BCTI Blog

Excel – The BEST Dynamic Data Validation Dropdown List (September 29, 2017) - Excel Data Validation lists are a great way to expedite data entry ensure consistent data entry reject undesirable information One of the few and far between shortcoming of Data Validation lists is the fact that with long lists, they have to exist somewhere in the workbook.  The existing list supplies the entries displayed when the […]
Excel – Create Named Ranges Quickly and Use Retroactively (September 28, 2017) - Excel Named Ranges are a great way to turn ugly, confusing formulas into easy to read statements.  Consider the following formula:     =($J$44 – $M$213) * $H$16    Without any context given to the formula, the viewer has no idea what this is supposed to produce.  The only obvious logic is that there is some subtraction […]
Insert a Chart in (virtually) Any Office App (September 15, 2017) - Whether you’re an avid user of Microsoft Word, PowerPoint, Outlook, or Excel, sooner or later you’ll have a need to insert a chart in your document, presentation, email or spreadsheet. In the old days, Excel was the master at creating charts, leaving the other Office applications a bit lacking in the arena of chart making.  […]
Office 2016 Template Library – Worth a Revisit (August 28, 2017) - If you have been a user of Microsoft Office going back to the “old days” of Office 95 thru Office 2003, one of the features you probably held in least regard were the templates.  Templates suffered several major disadvantages: Templates were stored on the local PC’s hard drive, which took up much needed storage space […]
Create a PowerPoint Presentation from a Word Outline (August 15, 2017) - Imagine you need to create a PowerPoint presentation but you are plagued with one or a combination of the below problems: You aren’t very well versed in the use of PowerPoint You don’t have PowerPoint installed on the computer you are using You have very little time to put a presentation together Now suppose you […]
Windows 10 – Switch Audio Output Devices FAST (August 4, 2017) - If your work entails somewhat repetitive tasks, there is nothing like having your favorite music playing in the background to help pass the time.  Working in an office, especially one with cubicles, employees are often discouraged or prohibited from playing music at their desks or workstations so as to not bother other employees.  It is […]
Excel – Consolidate Tool (July 28, 2017) - Suppose you have data stored in multiple locations but you wish to create a chart from all of the data sources as if they were stored as a single data source.  You could spend time manually copying and pasting the data into a single, unified list, but this may prove difficult if the data is […]
PowerPoint – Insert Video with Different Audio (July 21, 2017) - When inserting a video into PowerPoint, there are times that you want to replace the original audio track.  This can be for a variety of reasons: Original video has no accompanying audio Original audio is of poor quality Original audio contains content that violates copyright laws To replace the original audio track, perform the following […]
Excel – Display an Alert If a Sorted List Becomes Unsorted (July 14, 2017) - Excel has the ability to sort data in a variety of ways: Alphabetically Numerically Chronologically In addition, the data can be sorted in either ascending (smallest to largest) or descending (largest to smallest) order. Sorted lists are useful when printing or for scanning when looking for information.  It is far easier to locate an item […]
Excel – Adding Ordinal Suffixes to a List of Numbers (June 30, 2017) - An ordinal indicator is a character, or group of characters, that follows a number denoting that it is an ordinal number, as opposed to a cardinal number. For English speaking countries, this corresponds to the suffixes -st, -nd, -rd, -th in written ordinals (represented either on the line 1st, 2nd, 3rd, 4th or as superscript, […]
Merge Multiple Word Documents into a Single Word Document (June 20, 2017) - Imagine putting together a research paper, training manual, or business proposal.  These documents are often the culmination of several separate documents assembled by a team.  If each member of the team is working on their part off the project in their own project file, the time will come when all of the pieces of the […]
Outlook Meeting Invitations Deleted After Accepted… WHY?!?! (March 6, 2017) - Outlook is an indispensible application when it comes to maintaining communication with friends and coworkers.  Microsoft has packed an array of wonderful features into Outlook to assist in this endeavor.  However, there is an interesting “feature” in Outlook that, for some people, seems to go one step too far in helping and actually doubles back […]
Excel Color – Order of Application (Hierarchy) (February 20, 2017) - Have you ever wondered why Excel, on occasion, will or will not color a cell according to your instructions?  The reason may lie in the order by which Excel applies colors. Excel applies colors in layers.  These layers are comprised of three criteria: Colors automatically applied via the implementation of a Data Table Colors manually […]
iPhone – Make a Call Without Unlocking Your Phone (February 20, 2017) - Apple’s iOS 10 has some amazing new features.  Performing a simple Google search can reveal dozens upon dozens of neat tricks.  The following trick has become a fast favorite amongst iPhone users. Placing a call without unlocking your phone It is well known that you can make an emergency call to 9-1-1 on any cellphone […]
Restoring FIND Keyboard Shortcut in Word 2016 (February 17, 2017) - There’s nothing like a good keyboard shortcut to help improve workflow efficiency.  Most Office users quickly learn the standard keyboard shortcuts, like CTRL+S for SAVE or CTRL+P for PRINT, but what about CTRL+F for FIND?  This is a great way to bring up the FIND dialog box. Somewhere along the way, Microsoft, in its infinite […]
Excel IFS and SWITCH Function (Say Goodbye to Nested IFs and VLOOKUPS On Small Tables) (February 6, 2017) - Some of the first functions that a newly-titled “power user” learn are IF and VLOOKUP.  The IF function provides the user the ability to ask a question and perform action “A” for one answer and action “B” for another answer.  The catch is that any question posed must be answerable as either “true” or “false”. […]
Two-Way Lookup with the Intersection Operator (February 3, 2017) - Locating a specific item in a table based on two separate criteria can be a challenging task, even for the most ardent Excel user.  Take the following table, for example: Suppose you wish to locate the sales for the Southern region in the month of March.  You could try to write a sophisticated VLOOKUP with […]
VLOOKUP–Looking to the LEFT! (January 20, 2017) - Anyone who has ever used Excel and needed to query a table for information has no doubt used a VLOOKUP function.  VLOOKUP is one of the most widely used functions in all of Excel.  But even the most popular things in life are not without their shortcomings. The single most vexing problem for VLOOKUP users […]
How Big is a Spreadsheet? (January 10, 2017) - Have you ever wondered just how large an Excel spreadsheet really is? When it comes to entering data, Excel spreadsheets have grown larger and larger over the years.  In Excel’s infancy, spreadsheets were only 256 columns wide and 16,384 rows tall.  Over the years the number of columns remained at 256 but the height grew […]
Using Excel MODE Function to Return a Text Response (January 5, 2017) - Excel’s MODE function is a great tool for returning the most frequently occurring number in a set of numbers. But what if you want to return the most frequently occurring word in a list of words? MODE with Numbers Using the MODE function in Excel is quite simple; you point to a list of numbers […]
Add Dropbox to Office 2016 (December 23, 2016) - We’ve come a long way over the years when it comes to where we store our files.  In the 80’s and 90’s it was all about local storage; the venerable C:, or “C-drive”.  Then in the 2000’s, when connecting all of the office computers together on a network became the norm, companies told us not […]
Send “Out of Office” Replies from Outlook (December 20, 2016) - You can send automatic “Out of Office” replies from Outlook 2013 or Outlook 2016 for Windows whenever you want to let people who send you email know that you won’t be responding to their messages right away.There are two different ways to set up automatic replies. The method you choose depends on the type of […]
Conditional Formatting with Subtotals in Excel (December 9, 2016) - Just as there should be a Nobel Prize for the person who put a screwdriver in the end of a power drill, thus inventing the Power Screwdriver, there should be a Nobel Prize for merging the abilities of Conditional Formatting with the Subtotals tool in Excel. If you have ever used the Subtotals tool to […]
Never Miss Another Public Holiday (November 23, 2016) - In the spirit of Thanksgiving, this week’s blog post will deal with managing holidays in Outlook. Do you have trouble remembering which day Labor Day is on versus Memorial Day?   Ever show up for work one morning just to discover that the office is closed for President’s Day and you could have slept late?  Well, […]
Supercharge Your VLOOKUP Functions in Excel (November 18, 2016) - Any Excel user who considers themselves a “power user” cannot make such a statement with a straight face without knowing how to use VLOOKUP functions. Of all of the ways to retrieve information, the VLOOKUP function is without debate the most popular means of looking up and returning information from an Excel list.  It’s not […]
Create a Video in PowerPoint 2016 (October 26, 2016) - If you have ever been tasked with creating a video to demonstrate come process in an application, like creating pivot tables in Excel or performing a mail merge in Word, you probably had to spend a bit of money on some screen capturing software.  There are many great programs on the market designed specifically for […]
Office 2016–New Chart Types (October 12, 2016) - As a seasoned producer of charts, you have probably wondered why Microsoft has not included certain type of charts that have previously only been available via third-party add-on programs.  Microsoft has heard your calls and added some new and exciting charts to the Office 2016 charting engine.  These charts type are not just available in […]
QlikView–Creating a Master Calendar (October 10, 2016) - If you’ve ever struggled with constructing a master calendar for a QlikView data model, this post should help reduce your stress levels. If you’re unsure of what the purpose of a master calendar is, a master calendar helps reduce the redundancy of date information in a data set.  Imagine placing in a table a year’s […]
Apply Two Different Number Formats to a Single Field (Excel Pivot Tables) (September 30, 2016) - There comes a time in every Excel Power User’s life when they must deal with mixed content in a single field.  Examine the following table: The Value field (column E) contains a mixture of sale amounts (values as decimals) and discount rates (percentages.)  If a pivot table is constructed using this data, it can take […]
Missing Resources in Microsoft Project Resource Pools (September 8, 2016) - If you work with Microsoft Project and have ever leveraged the power of Resource Pools across multiple projects, you may have encountered a strange behavior when assigning those resources. When you share resources between a Resource Pool file (a dummy project file that typically has no tasks but is merely a container for holding resources) […]
iPhone Trick – View an Email Message While Composing Another (September 2, 2016) - Have you ever been composing an email message and realize that you have to check something in a different message?  If you’re like most people, you save the message you are currently composing as a draft, close the message, open the OTHER message to get the needed information, close THAT message, and then reopen the […]
Automatically Refresh Pivot Tables in Excel (August 26, 2016) - Excel PivotTables are one of the greatest tools in the spreadsheet user’s toolkit. However, there is one tiny bit of functionality that appears to be missing: the ability of pivot tables to automatically update when information in the source data changes. Most user’s see this as a glaring lack of functionality. There is, however a […]
10 Word Features that will Improve Your Workflow (August 16, 2016) - With so many features packed inside each Microsoft application, it’s easy to overlook some that can really add value to your daily workflow.  Below is a list of some of the features you may have never noticed but will quickly become part of your daily routine. Styles Styles are not only a great time-saver, but […]
Sandvik 2 BCTI Contributes $10,000 in Computer Application Training to Sandvik Mining (July 20, 2016) - BCTI has announced a $10,000 contribution for desktop application training to Sandvik Mining and Construction Co., Bristol, Va. BCTI is providing Sandvik employees desktop computer training, which includes Windows 7, Microsoft Word, and Microsoft Excel to assist them in finding new jobs as quickly as possible. “Giving back to the community is important to us […]
Welcome (July 18, 2016) - Welcome to BCTI blog