# jfn Excel & accounting tips

## Wednesday, October 12, 2016

## Friday, May 20, 2016

### How Excel counts business days, holidays and more

*Kumar Malyala asked if there’s a way in Excel to count the number of business days between two dates.*

It’s easy to find the number of days between two dates in Excel. You just subtract the earlier date from the later one. If you put

**July 19**in A1,

**May 5**in A2, and enter

**=a1-a2**in A3, you’ll get 75—the number of days between May 5 and July 19.

However, if you don’t want Saturdays and Sundays in your total, you’ll have to work a bit harder. And if you need to take holidays into consideration, it gets trickier still.

But not too tricky.

**[Have a tech question? Ask PCWorld Contributing Editor Lincoln Spector. Send your query to**

**answer@pcworld.com**

**.]**

With or without holidays, you’ll need to use the

*networkdays*function. And no, it has nothing to do with connectivity. The name means

*net workdays*, not

*network days.*

I tested this in Excel 2010 and 2016.

To find the number of weekdays between two dates, simply use the formula

**=networkdays(**Start_date,End_date

**)**, with

*Start_date*addressing the cell containing the earlier of the two dates, and

*End_date*addressing the later date. For instance, if you have

**April 16**in A1 and

**September 19**in A2,

**=networkdays(a1,a2)**will give you the answer 111.

Keep in mind that networkdays counts the Start_date in its result. If your Start_date is a Monday, and the End_date is the same week’s Friday, networkdays will return the number 5, not 4.

To subtract your office’s holidays from the networkdays results, create a list of company holidays somewhere on the spreadsheet (it can be on a different tab). The actual dates of the holidays should be listed in a column.

You’ll need to create what Excel calls a named range of those holiday dates. Select the dates, then go to the

*Formula*ribbon and pick

*Define Name*(Excel 2010) or

*Defined Names > Define Name*(Excel 2016). (For more on named ranges, read JD Sartain's detailed how-to.)

In the resulting dialog box, give the range the name

**holidays**.

Then add the argument

**holidays**to the end of the networkdays formula. In other words, instead of =networkdays(a1,a2), enter

**=networkdays(a1,a2,holidays)**.

The weekdays where your office is closed will no longer be counted in the formula.

Source: http://www.pcworld.com/article/3056908/software/how-excel-counts-business-days-holidays-and-more.html

## Monday, May 9, 2016

### How to Find Data in Access 2016

When you want to track down a particular record

Find is found — big surprise here — in the Find section of the Home tab, accompanied by a binoculars icon. You can also get to Find by pressing Ctrl+F to open the Find dialog box.

Access offers several tools for fine-tuning a Find. To use them, open the Find and Replace dialog box by either

Fine-tune your Search settings by clicking the down arrow next to the Search box and choosing the appropriate offering from the drop-down menu.

This works really well if you’re searching for a name, rather than just a word, so that

Limiting the search in this way is handy when you’re searching for dates, stock-keeping unit IDs, or any other field with quite a bit of specialized formatting.

Turn on Search Fields as Formatted by clicking the check box next to it.

Source: http://www.dummies.com/how-to/content/how-to-find-data-in-access-2016.html?cid=dn_article

*right now,*creating a query for the job is overkill. Fortunately, Access 2016 has a very simple way to find one specific piece of data in your project’s tables and forms: the Find command.Find is found — big surprise here — in the Find section of the Home tab, accompanied by a binoculars icon. You can also get to Find by pressing Ctrl+F to open the Find dialog box.

Although the Find command is pretty easy to use, knowing a
few tricks makes it even more powerful, and if you’re a Word or Excel
user, you’ll find the tricks helpful in those applications, too —
because the Find command is an Office-wide feature.

## Finding anything fast

Using the Find command is a very straightforward task. Here’s how it works:- Open the table or form you want to search.Note that the Find command works in Datasheet view and with Access forms and becomes available as soon as a table or form is opened.
- Click in the field that you want to search.The Find command searches the current field in all the records of the table, so be sure to click the correct field before starting the Find process. Access doesn’t care which record you click; as long as you’re on a record in the correct field, Access knows exactly which field you want to Find in.
- Start the Find command.You can either click the Find button in the Find section of the Home tab or press Ctrl+F.The Find and Replace dialog box opens, ready to serve you.
- Type the text you’re looking for in the Find What box.Take a moment to check your spelling before starting the search. Access is pretty smart, but it isn’t bright enough to figure out that you actually meant plumber when you typed plumer.The Find and Replace dialog box.
- Click Find Next to run your search.
- If the data you seek is in the active field, the Find command immediately tracks down the record you want.The cell containing the data you seek is highlighted.What if the first record that Access finds isn’t the one you’re looking for? Suppose you want the second, third, or the fourteenth
*John Smith*in the table? No problem; that’s why the Find and Replace dialog box has a Find Next button. Keep clicking Find Next until Access either works its way down to the record you want or tells you that it’s giving up the search. - If Find doesn’t locate anything, it laments its failure in a small dialog box, accompanied by this sad statement:Microsoft Access finished searching the records. The search item was not found.

If Find didn’t find what you were looking for, you have a couple of options:

- You can give up by clicking OK in the small dialog box to make it go away.
- You can check the search and try again (you’ll still have to click OK to get rid of the prompt dialog box). Here are things to check for after you’re back in the Find and Replace dialog box:
- Make sure that you clicked in the correct field and spelled everything correctly in the Find What box.You can also check the special Find options to see whether one of them is messing up your search.
- If you ended up changing the spelling or options, click Find Next again.

## Shifting Find into high gear

Sometimes just typing the data you need in the Find What box doesn’t produce the results you need:- You find too many records (and end up clicking the Find Next button endlessly to get to the one record you want).
- The records that match aren’t the ones you want.

Access offers several tools for fine-tuning a Find. To use them, open the Find and Replace dialog box by either

- Clicking the Find button on the Home tabor
- Pressing Ctrl+F

*exclude*what you’re looking for.### Look In

By default, Access looks for matches only in the*current*field — whichever field you clicked in before starting the Find command. To tell Access to search the entire table instead, choose Current Document from the Look In drop-down list.
To search the entire table, change Look In.

### Match

Your options are as follows:**Any Part of Field:**Allows a match anywhere in a field (finding*Richard, Ulrich,*and*Lifestyles of the Rich and Famous*). This is the default.**Whole Field:**This requires that the search terms (what you type in the Find What box) be the entirety of the field value. So*Rich*won’t find*Ulrich*,*Richlieu*,*Richard*, or*Richmond*. It finds only*Rich.***Start of Field:**Recognizes only those matches that start from the beginning of the field. So*Rich*finds*Richmond,*but not*Ulrich.*This option allows you to put in just part of a name, too, especially if you know only the beginning of a name or the start of an address.

Using the Match option.

### Search

If you’re finding too many matches, try limiting your search to one particular portion of the table with the help of the Search option. Search tells the Find command to look either- At all the records in the table (the default setting)or
- Up or down from the current record

Fine-tune your Search settings by clicking the down arrow next to the Search box and choosing the appropriate offering from the drop-down menu.

### Match Case

Checking the Match Case check box makes sure that the term you search for is*exactly*the same as the value stored in the database, including the same uppercase and lowercase characters.This works really well if you’re searching for a name, rather than just a word, so that

*rich custard topping*is not found when you search for (capital*R*)*Rich*in the entire table.### Search Fields as Formatted

This option instructs Access to look at the formatted version of the field rather than the actual data you typed. By “formatted” this means with formatting applied to the content – numbers formatted as percentages, dates set as short dates vs medium dates, and so on.Limiting the search in this way is handy when you’re searching for dates, stock-keeping unit IDs, or any other field with quite a bit of specialized formatting.

Turn on Search Fields as Formatted by clicking the check box next to it.

This setting doesn’t work with Match Case, so if Match
Case is checked, Search Fields as Formatted appears dimmed. In that
case, uncheck Match Case to bring back the Search Fields as Formatted
check box.

Most of the time, this option doesn’t make much difference. In fact,
the only time you probably care about this particular Find option is
when (or if) you search many highly formatted fields.Source: http://www.dummies.com/how-to/content/how-to-find-data-in-access-2016.html?cid=dn_article

### Putting Continuous Accounting to Work

My March 2 column on continuous accounting, “Stopping the Madcap Sprint to Close the Books,” apparently struck a chord, encouraging many readers to write thoughtful responses. Most wanted to know how continuous accounting “actually works.” This piece is my reply to these inquiries.

First a quick recap of my earlier column: Continuous accounting is a measured and balanced alternative to compressing a month’s (or more) worth of accounting tasks to close the books into a few days at the end of the month, quarter, or year. Rather, the work to perform such closing tasks is conducted incrementally by accountants

*during*the month, quarter, and year.

The benefits include a reduction in the pile-up of accounting work and the stress this causes; lower risk of financial irregularities; and an earlier opportunity for financial planning & analysis to deliver the business forecast.

*CFO*readers intrigued by these benefits wanted to know, as one wrote, “How does this actually work?”

To answer the question, let’s start with today’s inefficient workload procedures. As part of their bookkeeping, accountants in midsize and larger companies manually sift through thousands of spreadsheets generated by different systems. Without an automated system, it’s an uphill climb to methodically tick and tie the ledgers to confirm that every item is accounted for and properly connected to related items. This confirmation process is therefore pushed off to the end of the month, quarter, or year.

Another inefficiency derives from batch processing. When digital documents replaced paper-based accounting records, technological limitations caused the accounting work to be stored up during working hours and executed when computer systems were offline. As the volume of data expanded, the process took more time to run. Ultimately, the system was not ready to use when accountants required its use. Again, the solution was to push off the work.

What’s wrong here? Top-notch accounting talent is underutilized during non-peak periods and overworked during the peak period. To alleviate some of the workload, finance and accounting hires temp workers that eat up the budget.

These many inadequacies and the pains they inflicted — from burned-out accountants and pointless costs to an increased risk of errors, out-of-date results, and zero time for analysis — raise the obvious question: “What if we could account for the business at close at the speed of transactions?”

Well, the answer is, you can.

####
**And Here’s How **

The first thing to do is to rethink the internal paradigm of accounting, adjusting the mindset to consider the F&A
process as one of continuous improvement. The goal is to optimize the
accounting process one task at a time, then rinse and repeat. By
building automation and accuracy into the accounting process,
accountants are eliminating the time wasted performing redundant
exercises and fixing errors. In this voyage, automated technology is a
tool to achieve the ends; it is not the beginning of the journey.Great things are possible once a philosophy of continuous accounting is in place. Instead of bringing up all the data in one batch process as before, the batch is split up into a series of smaller tasks. Tasks are scheduled as early in the accounting process as possible and embedded within the everyday workflow of the accountants. By breaking the tasks into smaller pieces, the completion of these elements becomes routine, a process that takes its cue from agile software development. Tasks are automated where possible and standardized elsewhere.

The last step is to monitor the effectiveness of these process efficiency improvements through predictive analytics, measuring the time it takes to close the books, the total costs per full-time F&A employee, and the average number of days for an accountant to complete a selected assignment.

####
**Continuous Accounting in Action**

Sounds all well and good, but readers have asked for “actual
examples.” Let’s apply the above steps to a particular F&A task —
account reconciliations.The current state of many finance organizations is to perform the reconciliations at the end of the month, reducing the opportunity to conduct proactive analysis before then. The alternative is to split the batch process into smaller tasks, such as the daily reconciliation of

*key*accounts. To perform this task earlier, scheduled end-of-month reconciliations are shifted to event-based reconciliations, i.e., as they occur. Automation is used to automatically reconcile the key accounts and match these transactions. Accountants are now in the position to investigate anomalies associated with these reconciliations throughout the month, minimizing the period-end rush.

Let’s apply continuous accounting to another routine F&A task — intercompany accounting, the process of balancing the accounts between two different company branches to bring the balances to zero in the general ledger. The current state in most F&A organizations is to wait until the books are closed and entries are booked by these different entities before performing the task. Since the time it takes to record the transactions varies by entity, there’s no time to react to possible discrepancies, culminating in a rushed review at the period-end.

By dividing the batch process into smaller tasks, intercompany transactions can be recorded and approved on each entity’s books in real-time throughout the period. The task no longer waits until the end of the month, quarter, or year, as it is embedded along with the proper controls into daily workflows for automatic approval. By automating this process, accountants are freed to focus on value-added services to the CFO.

As the reader can see, continuous accounting is a philosophical approach, not simply a technological one. Only by first analyzing the current state of F&A can a vastly more efficient future state be designed, followed by its development and continuous improvement.

*Therese Tucker*

*is CEO of financial automation software provider*

*BlackLine*

*.*

Source: http://ww2.cfo.com/accounting/2016/05/putting-continuous-accounting-work/

Source: http://ww2.cfo.com/accounting/2016/05/putting-continuous-accounting-work/

## Saturday, April 23, 2016

### How to Use Quick Analysis in Excel 2016

When
you need to do a speedy analysis of your data in Excel 2016, consider
using the Quick Analysis feature. Here are some points to keep in mind
about Quick Analysis:

- When you select a range of cells, a small icon appears in the lower right corner of the selected area. This is the Quick Analysis icon, and clicking it opens a panel containing shortcuts to several types of common activities related to data analysis.
- Click on of the five headings to see the shortcuts available in that category. Then hover over one of the icons in that category to see the result previewed on your worksheet:
*Formatting:*These shortcuts point to conditional formatting options. For example, you could set up a range to make values under or over a certain amount appear in a different color or with a special icon adjacent.Open the Quick Analysis panel by clicking its icon. Then choose a category heading and click an icon for a command.*Charts:*These shortcuts generate common types of charts based on the selected data.Quick Analysis offers shortcuts for creating several common chart types.*Totals:*These shortcuts add the specified calculation to adjacent cells in the worksheet. For example, Sum adds a total row or column.Notice that there are separate icons here for rows vs. columns.Notice also that in this category there are more icons than can be displayed at once, so there are right and left arrows you can click to scroll through them.You can use Quick Analysis to add summary rows or columns.*Tables:*You can convert the range to a table for greater ease of analysis. You can also generate several different types of PivotTables via the shortcuts here. A*PivotTable*is a special view of the data that summarizes it by adding various types of calculations to it.The PivotTable icons aren’t well-differentiated, but you can point to one of the PivotTable icons to see a sample of how it will summarize the data in the selected range. If you choose one of the PivotTable views, it opens in its own separate sheet.You can convert the range to a table or apply one of several PivotTable specifications.*Sparklines:*Choose Sparklines to add mini-charts that show overall trends.

## Wednesday, April 6, 2016

### Need to Learn Excel? 10 Experts Will Teach You for Free!

No one denies that Excel is an incredibly powerful piece of software. Yet, learning how to use its more advanced features is far from easy. With that in mind, we’ve tracked down ten top Excel gurus who can help you master the advanced functions of Excel more easily.

In the past, we’ve recommended places where you can learn the basics of Excel. With these basics covered, you’ll be able to use Excel’s main data storage, organization, and manipulation features. We’ve also listed resources for figuring out Excel formulas, and the types of charts you should be using.

But if you want to take your knowledge even further, you’ll need reliable Excel specialists, who are willing to share their growing, in-depth knowledge of this program with you.

The following ten Excel gurus fit this description perfectly. Each guru regularly publishes step-by-step tutorials that walk you through these more advanced Excel features that you could otherwise be wrestling with for weeks.

## 1. Chandoo

Purna Duggirala (aka, “Chandoo”) is a minor celebrity in Excel circles. Since 2004, Chandoo has been regularly sharing everything he learns while working with Excel. His site, which aims to “make you awesome in Excel” now receives over 1.5m visitorsper month, and is Chandoo’s full-time project.

On the site you’ll find a popular Excel podcast, thriving forum, and a 75,000-strong mailing list. Among the main content on the site, you’ll find guides toanalyzing half a million customer complaints, to mastering pivot tables (which we’ve discussed before).

## 2. Mynda Treacy

Much of the content on Mynda Treacy’s site is available free of charge. It is made up of in-depth Excel tutorials accompanied by screenshots and videos to really drill home the lessons. These tutorials include more basic lessons, such asselecting multiple items from a data validation list, to advanced tutorials onPower Query reformats.

On the site, you’ll also find a range of top quality webinars run by other Excel professionals that you can stream for no cost.

## 3. Bill Jelen

Having been working with Excel for two decades, Bill Jelen (aka “Mr Excel”) is a true oracle of this software. But if that’s not enough, the huge Excel forum on Jelen’s site houses an army of Excel experts to answer any of your questions.

His topics cover everything imaginable, from the benefits of Power Pivots, totidying up messy data. And if you’re more of a visual learner, Jelen occasionally hosts webinars, and posts screen capture videos to his YouTube channel.

## 4. Mike Girvin

ExcelIsFun is the huge Excel YouTube channel run by Mike Girvin. With well over 2,000 Excel video tutorials, this is a channel that anyone who regularly works with Excel should subscribe to. Most of the videos are 7-15 minutes long, and are very easy to follow (provided you have a basic grasp of Excel already).

If you’ve never checked out Girvin’s channel (and his energetic teaching style) before, the range of videos can be overwhelming. Luckily, he’s sorted these into descriptive playlists to help you find your way around. For example, you can find 68 videos to help you master the SUMPRODUCT function and 139 videos for mastering VLOOKUP.

## 5. Jon Peltier

Jon Peltier has been working with Excel since 1995 and is a whizz at using Virtual Basic for Applications (VBA) and Visual Basic. That’s why he has been acknowledged as a Most Valuable Professional (MVP) by Microsoft every year since 2001. The skills he teaches enable Excel to “be integrated with PowerPoint, Word, and other applications to supplement its analysis and reporting capabilities”.

Peltier’s extensive site, PeltierTech.com focuses on a wide range of Excel functions, but specializes in advanced tutorials “for achieving charting effects that, at first glance, seem impossible”. These include tutorials for Bar-Line charts, Pivot Charts, and Dynamic Charts.

## 6. Debra Dagleish

Author Debra Dagleish’s regularly updated blog on Contextures focuses on detailed, relatively advanced Excel tutorials. These zoom in on underused functions like using sheet selectors with no macros, as well as fixing common problems like VLOOKUP sorting issues.

On top of a large number of tutorials, you’ll also find a huge number of free Excel file downloads and video tutorials to really help you master these more advanced functions.

## 7. John Walkenbach

Having authored over 50 books (including the Excel Bible series) and hundreds of articles, Walkenback has surely earned the title “Excel Guru”. Not surprisingly, his website, SpreadsheetPage is one of the most popular sites about Excel out there.

Walkenbach’s blog covers plenty of Excel updates, and issues that you may want to explore further. More relevant to this article, however, is the lengthy list ofExcel tips available. These cover topics from playing MP3 files from Excel, tocreating a worksheet map. There’s even a page of downloads, with sections specifically for developers, and Excel add-ins.

## 8. Dinesh Kumar Takyar

35,000 people find Dinesh Kumar Takyar’s Excel YouTube channel worth subscribing to, with many of his videos receiving hundreds of thousands of views. The aim of the channel is to teach you to use both Excel and VB proficiently, with many of the topics coming from Takyar’s own students and viewers.

The direct style gets straight to the point, with easy-to-follow screen recordings introducing you to a whole array of new features. Some of Takyar’s more popular tutorials include transferring data from one worksheet to another, creatingnotifications in Excel, and designing a user form.

## 9. John Michaloudis

MyExcelOnline is an incredible resource for learning Excel run by John Michaloudis. Michaloudis has been working with Excel for the past 15 years, and now produces tutorials accompanied by easy-to-follow GIFs and downloadable workbooks. These short tutorials can help you solve “real life business cases” quickly and easily. They range from learning to connect slicers to multiple pivot tables, to jumping to cell references within a formula.

On top of these rapid-fire tutorials, John also hosts a popular Excel podcast, where other gurus from this list have made an appearance (among many others).

## 10. Brad Edgar

The Excel Blog is Brad Edgar’s way of sharing his Excel enthusiasm with the rest of the world. His mission is to help Excel users find “confidence and reason in their employment or business endeavors”. The articles on Edgar’s site go further than many of the others in this list. Instead of focusing solely on step-by-step tutorials, you’ll also be treated to other types of content, such as Excel tips that’ll make you look smarter than your co-workers.

Despite the blog not being updated too regularly, the range of content types makes this a fascinating blog to follow. And if you need to download any sample data to help you with other tutorials, you can purchase some on the site, too.

## Where Else Do You Learn About Advanced Excel Functions?

The number of things you can do or problems you can solve with Excel is astounding. You can use basic formulas to solve real life problems, create dashboards to visualize complex data sets, and everything in between.

So no matter what you’re trying to achieve, the combined knowledge of these 10 Excel gurus will undoubtedly be enough to get you there. But there are still tons of fantastic resources we’ve not come across, let alone mentioned.

So, which Excel resources, and other Excel gurus, would you like to have seen on this list? Where do you go to solve your Excel problems, and to learn more about advanced Excel functions?

Source: http://www.makeuseof.com/tag/need-learn-excel-10-experts-will-teach-free/

Source: http://www.makeuseof.com/tag/need-learn-excel-10-experts-will-teach-free/

## Thursday, March 17, 2016

### Use INDEX and MATCH for simple database queries in Excel

The bigger your spreadsheet, the more you need these, which you can combine with SUM, AVERAGE, and MAX to refine your searches.

Originally, Excel was not designed to be a real database. Its early
database functions were limited in quantity and in quality. And because
every record in an Excel database is visible on the screen at once—which
means all in memory at once—the Excel databases had to be really small:
multiple fields with few records, or few fields with a lot of records;
and minimal calculations.

VLOOKUP (vertical) and HLOOKUP (horizontal) were the only functions available to query a database for specific information. For example, you could query to find and extract all records that contained sales greater $1000 but less than $5000—but only on flat files (only one database matrix).

Pivot Tables were developed so users could create relational databases, which are easier to query, use less memory, and provide more accurate results. If, however, you don’t have or need a relational database, but require more powerful and reliable database functions, try these for a start.

The MATCH function returns the position of a value (in a list, table, database). And, the INDEX-MATCH functions used together make extracting data from a table a breeze.

The syntax for the INDEX function is: INDEX(array, row_num, [column_num]). The array is the range of cells that you’re working with. Row_num is, obviously, the row number in the range that contains the data you’re looking for. Column_num is the column number in the range that contains the data you’re looking for. The INDEX formula doesn’t recognize column letters, so you must use numbers (counting from the left).

The syntax for the MATCH function is: MATCH(lookup_value, lookup_array, [match_type]). The lookup_value is the number or text you’re looking for, which can be a value, a logical value, or a cell reference. The lookup_array is the range of cells you’re working with. The match_type determines the MATCH function—that is, an exact match or the nearest match.

1. Select a location (cell or range of cells) for your queries (that is, functions and results), then move your cursor to that cell. For example: any cell on row 18.

2. Enter the INDEX function (preceded by the equal sign), plus an opening parenthesis, then highlight (or type) the database/table range like this: =INDEX(A2:I16

Note: If you want an absolute reference (which, in this case, means hard-coding the formula so when/if it’s copied, the range is not altered), press F4 once after each cell reference. You could also highlight the range: Just press F4 once after you select the full range, and the complete absolute reference symbols are added.

3. Next, enter a comma to separate the arguments (that is, the separate formula pieces); then enter the row number and a comma, followed by the column number (yes, it must be a number and not the usual column letter) and the right parenthesis (or just press Enter and let Excel add the ending parenthesis for you). The complete formula looks like this: =INDEX($A$2:$I$16,15,2).

Note: The row numbering starts with the first number in the range, not the first number on the spreadsheet. For example, even though the Cavalleria pirate ship is on Excel row 16, it’s actually row 15 in our formula because our range begins on A2 and goes through I16. If A2 is row 1, then A16 is row 15).

4. Note that the type of ship Norrington was looking for is a War Sloop.

1. Go to A2 and highlight the range A2 through I16.

2. From the Formulas tab, select

3. In the popup dialog box, enter a name for your range in the Name field box.

4. Next, enter the Scope (where the range is located), which is either the Workbook or one of the worksheets in the Workbook.

5. Enter a comment, if necessary.

6. And last, verify that the Refers To field displays the correct name and range, then click

7. If you’d like to verify that your range is, indeed, saved in Excel, try this little test: Press Ctrl+G (the GoTo command). Select

1. =SUM(INDEX(Ships, ,8)) equals 334, the total number of cannons, and

2. =AVERAGE(INDEX(Ships, ,8)) equals 22.27, or approximately 22.27 cannons per ship.

3. =SUM(INDEX(Ships, ,7)) equals 2350, the total number of all crew on all ships.

Why is there a comma, space, comma between Ships and the number 8, and what do these numbers mean? Ships is the Range (followed by a comma), the Row argument is blank (or a space) because Norrington wants all rows, and the 8 represents the 8th column over (which is column H, Cannons).

Some might ask, why not just enter the SUM and/or AVERAGE formulas at the bottom of those columns? In this tiny spreadsheet, yes, it would be just as easy. But if the spreadsheet has 5000 rows and 300 columns, you’ll want to use INDEX

Once the range is named, Norrington can open a blank spreadsheet in this same workbook and write his queries (formulas) in column B (which show the results instead of the formulas) with a description that defines those queries in column A. (Note: Column C shows the actual formulas that are in column B).

He doesn’t have to visually see his huge database of 5000 records or wait for several seconds while the formulas calculate. He can get all the information he needs from his Query Sheet. Remember, the bigger the spreadsheet, the slower it functions, especially if there are a lot of formulas.

1. =MAX(INDEX(Ships, ,7)) equals 300, the highest number of pirates on one of the ships

2. =INDEX($A$2:$A$16, MATCH(MAX(Ships), $G$2:G$16, 0)) equals the Royal James, the ship with the most pirates aboard

3. =INDEX($F$2:$F$16, MATCH(MAX(Ships), $G$2:G$16, 0)) equals Stede Bonnet, Captain of the Royal James with a pirate crew of 300

VLOOKUP (vertical) and HLOOKUP (horizontal) were the only functions available to query a database for specific information. For example, you could query to find and extract all records that contained sales greater $1000 but less than $5000—but only on flat files (only one database matrix).

Pivot Tables were developed so users could create relational databases, which are easier to query, use less memory, and provide more accurate results. If, however, you don’t have or need a relational database, but require more powerful and reliable database functions, try these for a start.

## Index, Match, and Index Match

In Excel, the INDEX function returns an item from a specific position (in a list, table, database).The MATCH function returns the position of a value (in a list, table, database). And, the INDEX-MATCH functions used together make extracting data from a table a breeze.

The syntax for the INDEX function is: INDEX(array, row_num, [column_num]). The array is the range of cells that you’re working with. Row_num is, obviously, the row number in the range that contains the data you’re looking for. Column_num is the column number in the range that contains the data you’re looking for. The INDEX formula doesn’t recognize column letters, so you must use numbers (counting from the left).

The syntax for the MATCH function is: MATCH(lookup_value, lookup_array, [match_type]). The lookup_value is the number or text you’re looking for, which can be a value, a logical value, or a cell reference. The lookup_array is the range of cells you’re working with. The match_type determines the MATCH function—that is, an exact match or the nearest match.

### A. INDEX function

In our example, the famous Commodore James Norrington has a spreadsheet that tracks all the pirate ships in the Caribbean. Norrington’s list is arranged by the ships’ combat formations, which match his nautical charts of the area. When he sees a vessel advancing, he enters the Index formula into his spreadsheet, so he can identify the ship and its capacities. In this first query, Norrington wants to know the type of ship that’s advancing.1. Select a location (cell or range of cells) for your queries (that is, functions and results), then move your cursor to that cell. For example: any cell on row 18.

2. Enter the INDEX function (preceded by the equal sign), plus an opening parenthesis, then highlight (or type) the database/table range like this: =INDEX(A2:I16

Note: If you want an absolute reference (which, in this case, means hard-coding the formula so when/if it’s copied, the range is not altered), press F4 once after each cell reference. You could also highlight the range: Just press F4 once after you select the full range, and the complete absolute reference symbols are added.

3. Next, enter a comma to separate the arguments (that is, the separate formula pieces); then enter the row number and a comma, followed by the column number (yes, it must be a number and not the usual column letter) and the right parenthesis (or just press Enter and let Excel add the ending parenthesis for you). The complete formula looks like this: =INDEX($A$2:$I$16,15,2).

Note: The row numbering starts with the first number in the range, not the first number on the spreadsheet. For example, even though the Cavalleria pirate ship is on Excel row 16, it’s actually row 15 in our formula because our range begins on A2 and goes through I16. If A2 is row 1, then A16 is row 15).

4. Note that the type of ship Norrington was looking for is a War Sloop.

### B. INDEX ranges

Now, we can do lots more with this database table. You don’t need to redefine the range every time you want to know something. To make it easy, we’ll define the range one time and then name it. Then we can just put the name of the range in our formulas.1. Go to A2 and highlight the range A2 through I16.

2. From the Formulas tab, select

*Define Name*from the Defined Names group.

3. In the popup dialog box, enter a name for your range in the Name field box.

4. Next, enter the Scope (where the range is located), which is either the Workbook or one of the worksheets in the Workbook.

5. Enter a comment, if necessary.

6. And last, verify that the Refers To field displays the correct name and range, then click

*OK*.

7. If you’d like to verify that your range is, indeed, saved in Excel, try this little test: Press Ctrl+G (the GoTo command). Select

*Ships*in the GoTo dialog, then click

*OK*, and Excel re-highlights the range A2:I16.

### C. INDEX with SUM & AVERAGE formulas

Norrington is assessing the fleet’s battle capabilities. First he wants to know how many cannons the pirates have, the average number of cannons per ship, and the total number of crew manning all those pirate ships. He enters the following formulas:1. =SUM(INDEX(Ships, ,8)) equals 334, the total number of cannons, and

2. =AVERAGE(INDEX(Ships, ,8)) equals 22.27, or approximately 22.27 cannons per ship.

3. =SUM(INDEX(Ships, ,7)) equals 2350, the total number of all crew on all ships.

Why is there a comma, space, comma between Ships and the number 8, and what do these numbers mean? Ships is the Range (followed by a comma), the Row argument is blank (or a space) because Norrington wants all rows, and the 8 represents the 8th column over (which is column H, Cannons).

Some might ask, why not just enter the SUM and/or AVERAGE formulas at the bottom of those columns? In this tiny spreadsheet, yes, it would be just as easy. But if the spreadsheet has 5000 rows and 300 columns, you’ll want to use INDEX

Once the range is named, Norrington can open a blank spreadsheet in this same workbook and write his queries (formulas) in column B (which show the results instead of the formulas) with a description that defines those queries in column A. (Note: Column C shows the actual formulas that are in column B).

He doesn’t have to visually see his huge database of 5000 records or wait for several seconds while the formulas calculate. He can get all the information he needs from his Query Sheet. Remember, the bigger the spreadsheet, the slower it functions, especially if there are a lot of formulas.

### D. INDEX MATCH with MAX

Now, Norrington wants to know how many pirates are on the most populated ship, and which ship is it? He uses the INDEX with MAX formula to get the highest number of pirates, but he also needs to know which ship is carrying them. So he uses the INDEX/MATCH with MAX formula to find out which ship has the most pirates on board.1. =MAX(INDEX(Ships, ,7)) equals 300, the highest number of pirates on one of the ships

2. =INDEX($A$2:$A$16, MATCH(MAX(Ships), $G$2:G$16, 0)) equals the Royal James, the ship with the most pirates aboard

3. =INDEX($F$2:$F$16, MATCH(MAX(Ships), $G$2:G$16, 0)) equals Stede Bonnet, Captain of the Royal James with a pirate crew of 300

Subscribe to:
Posts (Atom)