Wednesday, June 21, 2017

How to use Microsoft Excel's Conditional Formatting

PCWORLD- Set up your spreadsheet to highlight important data automatically.
Microsoft Excel's conditional formatting is a wonderful "automatic" feature that allows you to formats cells based on the value of those cells or the value of the formulas in those cells. For example, you can specify that all the sales totals in your spreadsheet that exceed $5,000 are highlighted in yellow; or all the dates prior to the current year use a dark-green font; or use a shape or ratings icon to flag all duplicate values above 12,000. The options are endless and, in addition to all the preset formats, you can create your own custom formatting rules.
The best thing about this feature is that it provides a quick snapshot of your spreadsheet when you view it or show it to others. Because the formatting is based on values, you don't have to do anything to make it work except update your data.
A. Format cells that meet these value conditions

1. Open a sales spreadsheet or enter a dozen names with sales totals for the first four months of 2017; that is, Jan, Feb, Mar, and Apr.
2. Highlight the Jan column of sales totals.
3. Select Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
4. Excel displays the Greater Than dialog box. In the field box under Format cells that are GREATER THAN, enter a number (for this example, 5000) and click OK.
5. Note that all the sales totals in column B that are greater than $5,000 are now highlighted in light green with a dark-green font.

01 highlight all cells greater than 5000 PC World / JD Sartain
Format all cells greater than 5000.
6. Use the same instructions above to highlight and format cells that are less than 5,000.

02 highlight all cells less than 5000 PC World / JD Sartain
Format all cells less than 5000.
7. The other options that apply to values are Format Cells BETWEEN two numbers, Format Cells that are EQUAL TO a specific number, and Format Cells that CONTAIN Duplicate or Unique values. Note the highlighted cells in the Mar and Apr columns.

03 highlight cells between two numbers equal to or containing PC World / JD Sartain
Format cells between two numbers, equal to a specific number, or containing unique or duplicate values.
B. Format cells that contain specific text
You can also use the Conditional Formatting feature to make certain text (letters, word, phrases, etc.) stand out.
1. Highlight the first column (A) of names.
2. Select Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
3. Excel displays the Text that Contains dialog box. In the field box under Format Cells that Contain the Text, enter the text you'd like to highlight (e.g., Smith) and click OK.
4. Note that all the Smith names are now highlighted in light red with a dark-red font.

04 format all cells that contain specific text PC World / JD Sartain
Format all cells that contain specific text.
C. Format cells that contain a Date within a range
1. Enter some dates in column F.
2. Select Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring.
3. Excel displays the A Date Occurring dialog box. In the field box under Format Cells that Contain a Date Occurring, enter a date range such as Today, Yesterday, Tomorrow, This Month, Next Month, etc., and click OK.
4. Note that all the dates for April are now highlighted in light red with a dark-red font.

05 format cells that contain a date within a range PC World / JD Sartain
Format cells that contain a Date within a range.
D. Top/Bottom rules
The next section of Conditional Formatting features Top/Bottom rules, which provides formatting for the Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, and Below Average items. The Top and Bottom Percent and the Top and Bottom Items are self explanatory, right? In each highlighted range, Excel formats the highest or lowest numbers and the highest or lowest percent.
1. Highlight column B.
2. Select Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items.
3. Excel displays the Top 10 Items dialog. The system defaults to 10, then prompts for a preset format. Select Light Red Fill with Dark Red Text, and click OK.
4. Next, highlight column C and follow the instructions above to format the Bottom 10 Items.

06 format cells for the top or bottom 10 PC World / JD Sartain
Format cells for the Top 10 or Bottom 10 Items in a range.
5. Highlight column D.
6. Select Home > Conditional Formatting > Top/Bottom Rules > Top 10%.
7. Excel displays the Top 10% dialog. The system defaults to 10, then prompts you for a preset format. Select Yellow Fill with Dark Yellow Text, and click OK.
8. Next, highlight column E and follow the instructions above to format the Bottom 10%.
Note: You can enter any number between 1 and 9999 for the Top and Bottom Items and any number between 1 and 999 for the Top and Bottom percent.

07 format cells for the top or bottom 10 percent PC World / JD Sartain
Format cells for the Top 10 or Bottom 10 percent in a range.
9. The Above and Below Average options are calculated like this: Excel averages the values in the highlighted column and determines the "average" number. In column B, the average is $3231. In column C, the average is 5523.50 You can check this by using the command =AVERAGE(B2:B13) and/or =AVERAGE(C2:C13).
10. Highlight column B for the Above Average formats and column C for the Below Average formats.
11. Follow the instructions above to select a preset format for each column.

08 format cells for the abovebelow average values PC World / JD Sartain
Format cells for the Above and Below Average values in a range.
E. Data Bars & Color Scales
Data Bars and Color Scales are formatting tools that spice up your spreadsheets with visuals that mean something. For example, Data Bars compare values—obviously the longest bars represent the highest values and the shortest bars denote the smaller values. Conversely, the Color Scales show a range between the highest, lowest, and mid-range values.
1. Highlight column B and choose a Gradient Fill Data Bar from Conditional Formatting > Data Bars.
2. Highlight column C and choose a Solid Fill Data Bar from Conditional Formatting > Data Bars.
3. Notice that the Data Bars function like a horizontal bar chart inside each cell. Expand the column width to see a better visual for the Data Bars.
4. Highlight column D and choose a Color Scale from Conditional Formatting > Color Scales, then highlight column E and choose a different Color Scale.
5. Notice how the colors change when the numbers break, and note the difference between columns D (Mar) and E (April).

09 use data barscolor scales to spice up your spreadsheets PC World / JD Sartain
Use Data Bars & Color Scales to spice up your spreadsheets with visuals that mean something.
E. Icon Sets
The Icon Sets also show a range of highest, lowest, and midrange values.
1. For example, highlight column B and choose Home > Conditional Formatting > Icon Sets > Shapes and choose the first set of dots on the list. Notice that Excel averaged out the numbers in the list and determined that all 1000s and 2000s are marked with red dots, 3000s and 4000s have yellow dots, and 5000s have green dots.
2. Highlight columns C, D, and E and select one Icon Set from each of the remaining three sets. Notice the difference between how the values are grouped in the Icon Sets with three icons, four icons, and five icons.

10 icon sets presets provide three fourfive levels of formatting copy PC World / JD Sartain
Preset Icon Sets provide three, four, and five levels of formatting.
Note: All the Icon Sets in this group: Directional, Shapes, Indicators, and Ratings provide three, four, and/or five levels of formatting, which again, determine the highest, lowest, and midrange values. You can easily change the formatting levels to more accurately fit your specific needs.
In fact, you can customize all of the Conditional Formatting features using self-defined rules that are easy to create through Excel's simple "walk-through" menus. We'll cover that next.

Source: http://www.pcworld.com/article/3181010/software/how-to-use-microsoft-excels-conditional-formatting.html#tk.rss_howto

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.
0516 work days
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.)
0516 holiday list
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.
by Lincoln Spector Contributing Editor



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 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:
  1. 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.
  2. 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.
  3. 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.
  4. 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.
     
     
    The Find and Replace dialog box.
  5. 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.
The best way to reduce the number of wrong matches is to add more details to your search, which will reduce the number of matches and maybe give you just that one record you need to find.
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 tab
    or
  • Pressing Ctrl+F
If your Find command isn’t working the way you think it should, check the following options. Odds are that at least one of these options is set to 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.
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.
To change the Match setting, click the down arrow next to the Match field and then make your choice from the drop-down menu that appears.
Using the Match option.
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
Clicking a record halfway through the table and then telling Access to search Down from there confines your search to the bottom part of the table.
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


Therese Tucker
Therese Tucker

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/

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 ico
       
       
      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.
       
       
      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.
      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.
       
       
      You can convert the range to a table or apply one of several PivotTable specifications.
    • Sparklines: Sparklines are mini-charts placed in single cells. They can summarize the trend of the data in adjacent cells. They are most relevant when the data you want to trend appears from left to right in adjacent columns.


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.
Chandoo
On the site you’ll find a popular Excel podcastthriving 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.
Mynda Treacy
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.
Bill Jelen
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).
Mike Girvin
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”.
Jon Peltier
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 chartsPivot 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.
Debra Dalgleish
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.
john Walkenbach
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.
Dinesh Kumar Takyar
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.
John Michaloudis
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.
Brad Edgar
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 problemscreate 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/