Tips for Excel

image

Sometimes a refresher on tips and tricks is a very good thing.  We get stuck in our rut of how we use apps and how we process data.  Here are some of my favorite Excel tips.

  • Add a Total Row without writing a formula. Just click anywhere inside an Excel table. Then press Ctrl+Shift+T and Excel will add the Total Row to the bottom.
  • Select any cell in your data range and press Alt+F1. Voila! You now have a chart!
  • The Status Bar shows averages, counts, and sums — without typing out complicated formulas! Just select some cells in your table, and then gaze your eyes on the lower right-hand corner of the Excel window. There you’ll see instant stats about your selection. See below.

image

  • Select any range in a table or PivotTable, and go to Insert > Slicer. Select the column you want to filter by. When you’re done, you’ve got handy buttons that let you filter.

Try these tips to make Excel more fun.

Use Fuzzy Lookup for Excel Instead of VLOOKUP

image

Fuzzy Lookup is Microsoft’s add in for Excel Tables that give brilliant results!

Using Excel’s VLOOKUP tool can be frustrating for finance folks and business analysts because it requires an exact match to get the link between two sets of information.  For example, if you look up J Hirst and the other information shows Hirst, J, you will not get a match.

Enter Fuzzy Lookup.  Microsoft designed Fuzzy Lookup to work only within Excel Tables so all data must be converted to tables to start the process.  My thought is that data is easier to work with in tables, anyway.  Fuzzy Lookup will match J Hirst with Hirst, J, and you can ask Excel to check again for all matches.  The match rate is quite high.  Here is what Microsoft says about Fuzzy Lookup…

The Fuzzy Lookup Add-In for Excel was developed by Microsoft Research and performs fuzzy matching of textual data in Microsoft Excel. It can be used to identify fuzzy duplicate rows within a single table or to fuzzy join similar rows between two different tables. The matching is robust to a wide variety of errors including spelling mistakes, abbreviations, synonyms and added/missing data. For instance, it might detect that the rows “Mr. Andrew Hill”, “Hill, Andrew R.” and “Andy Hill” all refer to the same underlying entity, returning a similarity score along with each match. While the default configuration works well for a wide variety of textual data, such as product names or customer addresses, the matching may also be customized for specific domains or languages.

Before you can use Fuzzy Lookup, you must download it from Microsoft and install it on your computer.  The link is http://www.microsoft.com/en-us/download/details.aspx?id=15011

Fuzzy Lookup is a super tool that will make your analyzing of data so much easier.

Largest FREE Microsoft eBook Giveaway!

image

image

Figure 1 & 2 from the site https://logs.msdn.microsoft.com

Check it out!   Tons of Free Microsoft eBooks.  The list of all books and the download selection is located at the link below.

https://blogs.msdn.microsoft.com/mssmallbiz/2017/07/11/largest-free-microsoft-ebook-giveaway-im-giving-away-millions-of-free-microsoft-ebooks-again-including-windows-10-office-365-office-2016-power-bi-azure-windows-8-1-office-2013-sharepo/ 

Note from Eric:

It’s that time of year again, and today I am kicking off my annual FREE MICROSOFT EBOOK GIVEAWAY extravaganza! And this time, I’m posting MORE FREE EBOOKS in this post than I ever have in any of my previous giveaways! Based on this, you will notice something different about this year’s post… In previous years, I included images of the eBook covers next to each item; however, I would also get feedback from people on the amount of time to load all of the images. So, this year I decided to streamline the list to make it easier to get to the eBooks you want. You will now find a simple list, organized by category, with the download links in the Format column so you can choose which format you want to download.

  1. How many can you download?
    • ANSWER: As many as you want! This is a FREE eBook giveaway, so please download as many as interest you.
  2. Wow, there are a LOT listed here. Is there a way to download all of them at once?
    • ANSWER: Yes, please see the note below on how to do this.
  3. Can I share a link to your post to let others know about this giveaway?
    • ANSWER: Yes, please do share the good news with anyone you feel could benefit from this.
  4. I know you said they are “Free,” but what’s the catch?
    • ANSWER: There is no catch. They really are FREE. This consider it a, “Thank you,” for being a reader of my blog and a customer or partner of Microsoft.
  5. Ok, so if they are free and you’re encouraging us to share this with others, can I post a link to your post here on sites like Reddit, FatWallet, and other deal share sites to let them know, or is that asking too much?
    • ANSWER: Please do. In fact, I would encourage you to share a link to this post on any deal site you feel their users could benefit from the FREE eBooks and resources included below. Again, I WANT to give away MILLIONS of FREE eBooks!
  6. Are these “time-bombed” versions of the eBooks that stop working after a certain amount of time or reads?
    • ANSWER: No, these are the full resources for you to use.

Jet Reports New Release for Dynamics GP and NAV

                        clip_image002

Jet Reports Canada sent out a notice this morning announcing that Jet Reports has released a new build.  All the details are below.

Three Excel Tricks for Lining up Data for Import into Dynamics NAV, GP, AX

image

Recently, I have had to import lots of different data for clients.  They give me Excel worksheets that are full of names, numbers and telephone numbers.  If you are working with Dynamics NAV or GP, you know that some of the fields can be very fussy in the import.  Dynamics AX is more forgiving.  I clean up the data, and get it ready. 

Most clients ask me how I clean up columns, especially phone numbers. If you use Excel a lot, you will know these formulas or tricks.  This post is for those who do not have to do this very often, and need some help.

1. Using Excel to change Words from All CAP to All lowercase, or to PROPER

    • you can use the UPPER, LOWER, or PROPER functions to automatically change the case of existing text to uppercase, lowercase, or proper case.
    • First, insert a temporary column next to the column that contains the text you want to convert
    • In the temporary column, for example,  cell B2, type =PROPER(A2)
    • The other choices are  =UPPER(A2) or  =LOWER(A2)

Now, all of the names, countries, states, provinces, etc. can have standardized type.

2. Removing spaces and symbols in a phone number

    • Often we get a list of telephone numbers that look like this

image

    • To get rid of the (, ), –, Ext. 0000, use the find and replace function
    • To get rid of the space, use the find and replace, using the space bar
      1. Press Ctrl + Space to select all cells in a column.
      2. Press Ctrl + H to open the “Find & Replace” dialog box.
      3. Press Space bar in the Find What field and make sure the “Replace with” field is empty.

3.  Filling in the extension on a phone number

    • Now  you want to add trailing zeroes to make all the numbers with a fixed length, such as 14 digits for a telephone number in Canada and the US
    • Create a blank column right of the existing phone number
    • Use the EXCEL function =A2&REPT(“0”,14-LEN(A2)) where “A” represents the column with the telephone number in it
    • The results are

image

      And, the number now becomes easy to import.

Microsoft Now Has Quick Start Guides For Office 2016

image

I will be the first to admit that I like having manuals to read for specific information.  Microsoft has now authored documents called Quick Starts for Office 2016.   You can see the documents at https://support.office.com/en-us/article/Office-2016-Quick-Starts-25f909da-3e76-443d-94f4-6cdf7dedc51e.

Here are the different versions available.

Resources Online

Download in PDF

Read in Office Sway

For Mac users, the following Quick Start guides exist.  The Office 2016 guides are available to download in PDF.  They include Word, Excel, Outlook, PowerPoint, and OneNote. 

Skype for Business, Mac, and Mobile

Thinking about whether to upgrade to Office 2016?  Take a look at the resource guides to help you decide. 

Power BI Desktop Updates At October 31 2016

Amanda Cofsky, Program Manager at Microsoft, announced the new desktop release of Power BI on October 31.  You can read it at https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-feature-summary/.

Here’s the complete list of October updates from Cofsky’s blog:

Report View

Analytics

Data Connectors

Query Editing Improvements

Other Improvements

The blog also contains simple instructions for using the DATE SLICER, REPORT GRIDLINES, DATA LABEL, and MAP FORMATTING OPTIONS.

Here Is A List of Helpful Excel HOW TO Articles

image Graphic from news.techgenie.com 

I love it when people take the time to write up step-by-step HOW TO articles or blogs.  And, I like Excel HOW TO articles because it is an easy way to give a tutorial to a client when I need them to create/fix/organize data for loading into an ERP system. 

Recently, I have found quite a few key Excel HOW TO procedures.  As I like to keep things where I can easily find them, I am putting them on my blog so that others have the list, too.

Here we go.

1. Copy a formula in Excel    http://www.wikihow.com/Copy-Formulas-in-Excel

2. Excel Data Validation for Dates  (This is awesome!) http://www.contextures.com/exceldatavalidationdates.html#download

3. How to find duplicates in Excel and numbers (uses conditionals and COUNTIF)  http://www.makeuseof.com/tag/find-duplicates-excel-numbers/

4. All about Pivot Tables http://www.excel-easy.com/data-analysis/pivot-tables.html or at https://fiveminutelessons.com/learn-microsoft-excel/how-create-pivot-table-excel

5. Eleven advanced Excel tricks – Vlookup, Index match,  3D Sum, $ sign, Ampersand symbol, arrays, goal seek, pivot tables, http://www.businessinsider.com/excel-tricks-vlookup-index-match-pivot-tables-array-2013-5#-1

6. How to split data from one column to two http://smallbusiness.chron.com/split-excel-data-two-columns-54661.html or https://www.ablebits.com/office-addins-blog/2014/02/27/split-cells-excel/

7. Link data in two workbooks https://www.youtube.com/watch?v=-foPogO3QNE

8. General Excel training from Microsoft https://support.office.com/en-us/article/Excel-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb?ui=en-US&rs=en-US&ad=US 

Jet Express for Excel For Microsoft Dynamics GP

image

Dynamics GP reporting is changing and improving!  For all Dynamics GP users who have been finding the boundaries in reporting with Microsoft Report Manager, get excited!  Jet Reports and Microsoft have partnered up to create Jet Express For Excel.  The announcement was made in May.  You can read Tara Grant’s blog here.

https://www.jetreports.com/blog/2016/05/31/microsoft-and-jet-reports-partner-to-offer-flexible-reporting-included-with-microsoft-dynamics-gp/

The big news is that it is available in June 2016, and there are over 20 Jet Express report templates, built for Microsoft Dynamics GP.  According to Grant, these reports provide insight into sales, finance, A/P, A/R, inventory and more.

The Jet Express for Excel site is at https://jetreports.com/l/express-splash.htmlThe product is not up on the site yet, but should be soon.  You can see a recording of the pre-launch webinar at the site, and download the feature guide and comparison sheet.

Dynamics CRM 2016 Available in Q4 2015

image

Dynamics CRM 2016 will be generally available in the fourth quarter of this year, delivering the “the most comprehensive upgrade ever” for the platform, Microsoft announced on Tuesday.  See the announcement at http://news.microsoft.com/2015/09/08/microsoft-reinvents-productivity-with-upcoming-release-of-customer-engagement-solution/.

The new enhancements are designed to:

  • increase productivity for organizations by providing a simple and seamless experience across their Dynamics CRM application
  • easier integration with email (there will be a CRM app inside Outlook),
  • easier integration with Excel and Dynamics CRM 2016 will offer an enhanced Excel experience including new Excel templates to automate tasks such as managing sales forecasts, for example.
  • easier integration with OneDrive for Business for easier task management,
  • an enhanced mobile experience for the worker on the go, and,
  • enriched data and analytics,

Businesses will benefit from this new version of CRM.