Archive for Nifty Tip
NIFTY TIP #5: Adding or Subtracting Amounts from Different Ranges
Problem: You need to add or subtract amounts from different ranges (SUMIF function). Solution: Insert two SUMIF formulas and combine them into a single formula: Insert the SUMIF formula to total the amounts based on the criterion 701 into cell E2. Insert the SUMIF formula to total the amounts based on the criterion 300 into [...]
Nifty Tip #4: Organizing the Data Table for Proper Use of the VLOOKUP Formula
The VLOOKUP formula searches for the lookup criteria in the leftmost column of the data table. It is recommended that the whole sheet be used as the data table, so that the VLOOKUP will automatically look at column A as the leftmost column. Defining a Name for the sheet (to use it as the Table_array, [...]
Nifty Tip #3: CONCATENATE
A couple of the last Nifty Tips we’ve talked about were about splitting combined cells. That’s great, and it’s often useful, but what if you want to combine the contents of a cell? Maybe you’re writing a letter or sending an invoice, and it would just be easier to have someone’s name be in one [...]
Nifty Tip #2: Combining the VLOOKUP and MATCH formulas
The VLOOKUP formula returns data from any column you choose in the data table. All you have to do is change the number of the column in the third argument, right? Well, it sounds easy, but there is a little catch. How can you determine the number of a column in a data table that [...]
Figart Consulting’s Excel Nifty Tip #1.5
NIFTY TIP # 1.5: Splitting a Full Name into Two Separate Cells Problem: You have inherited a mailing list with hundreds of names. You’ll need to be able to sort the list alphabetically and you notice that the individuals’ first and last names are together in the same cell. Your blood pressure rises as [...]
Figart Consulting’s Excel Nifty Tip #1
Figart Consulting will be offering a free series of Nifty Tips to help you solve common office problems without strangling your co-workers, clients or supervisors. Since so many of these issues involve Excel, the series will start there. If a solution is specific to a software version, we will make sure to make that clear [...]