Amazon References

Wednesday, August 9, 2017

Must Have Spreadsheet Templates, Calculators, and Calendars


Let me tell you something. This is by far the best library of Excel resources we have found on the world wide web. 

Since 2003, Vertex42® has been creating professionally designed spreadsheet templates for business, personal, home, and educational use. We love Microsoft Excel®, but are also a leading provider of templates for OpenOffice.org and Google Sheets. Our collection of financial calculators include some of the most powerful and user-friendly debt reduction and money management tools you can find. 

We also provide a large selection of templates for Microsoft Word® such as resumes, letters, or a business plan template that includes financial statements in Excel.

Thursday, July 13, 2017

Understanding Excel's conditional formatting rules can support steer clear of unintended results


scanrailistock-469667229.jpg
image: iStockphoto.com/scanrail
Excel's conditional formatting is a magnificent and versatile device that means that you can dynamically handle formatting. for example, you could put in force a rule that shows a worth in pink if it reaches a threshold of 500 or superior. otherwise you could reveal a complete checklist in yellow when an order date is older than two weeks. however now and again the rules you practice do not display the results you supposed. this may turn up when ambiguity exists between guidelines. listed here, i may clarify how Excel applies rules. With a higher figuring out of these rules, you're going to evade unintended results.
i am the usage of Excel 2016 on a windows 10 64-bit equipment. for your comfort, that you could down load the demonstration .xlsx or .xls file. Conditional formatting is obtainable in version 2000 and later, however the characteristic is extra accommodating within the Ribbon types. that you may view conditional codecs in the browser edition, and that they update correctly for those who change values. however, you can not build a rule within the browser; you'll need Excel's computing device version to build conditional formatting suggestions.
SEE: 10 cool ways to use Excel's conditional formatting characteristic 

What's a conditional formatting rule?


A conditional formatting rule is a dynamic application of formatting. each and every rule has two accessories that you specify:

  • A conditional expression that returns true or false.
  • The format(s) that Excel applies when the conditional expression returns true.

  • When the conditional expression returns actual, Excel applies the conditional structure. if you change the outcomes of the conditional expression to false through changing a value, Excel gets rid of the conditional format. The format updates instantly.
    you are going to run into instances where this function returns unexpected effects. First, the good judgment expressed by means of numerous guidelines can introduce ambiguity. In other words, two rules can fulfill the identical circumstance. 2nd, when making use of the identical structure, priority matters. You may even come across both situations in the equal set of guidelines.
    which you can add varied suggestions, however they're no longer self sustaining. Excel applies your guidelines via order of priority. initially, you assess priority: the rule of thumb you enter first has the least precedence, and the rule of thumb you enter ultimate has the highest priority. you can regulate this order at any time, and you can practice the cease alternative to any rule to steer clear of additional software when a particular circumstance is met. moreover, conditional formats take precedence over direct (manual) codecs. in case you eradicate a conditional formatting rule, direct codecs will continue to be.
    Getting a single rule to work is often convenient. Getting distinctive rules to work collectively as you plan requires the correct utility of those rules of precedence.
    pointing out your circumstances
    it's as much as you to manipulate Excel's priority suggestions to get the preferred effects. Let's study a simple set of circumstances you propose to follow to the fundamental records set shown in determine A:

  • If there's no date in column C, display a blue fill colour for the entire row.
  • If the quantity price in column E is more suitable than 500, display a red fill colour to the telephone.
  • If the quantity value in column E is more advantageous than 100, reveal a eco-friendly fill colour to the mobile.

  • each rule appears exclusive, however we will expose their conflicts as we are trying to observe these intentions to the records.
    determine A
    excelconditionalformata.jpg
    we will apply three simple conditional formatting guidelines to this records. Rule 1
    Let's start by using making use of the first rule: If there isn't any date in column C, display a blue fill color for the whole row. that you can accomplish that as follows:

  • choose C5:E9. The latitude you initially select is critical to your expectations. during this case, the expectation is to apply the format to the complete row. therefore, opt for all columns (and rows) on your facts set.
  • click on the home tab.
  • in the styles group, click Conditional Formatting after which opt for New Rule.
  • within the precise pane of the resulting dialog, choose Use A formulation To check Which Cells To format.
  • in the Edit the rule Description area, enter right here components:=IsBlank($C5)
  • click the layout button.
  • click on the Fill tab, select blue, and click ok. determine B indicates the conditional expression and the structure.
  • click on ok to come back to the sheet and follow the conditional format. thus far, so decent. The ensuing layout, additionally shown in figure B, is doubtless what you anticipated; rows 6 and seven are blue because C6 and C7 are blank.
  • figure B 
    excelconditionalformatb.jpg
    The dialog shows the expression you need to evaluate and the layout you are looking to apply when that expression equals genuine. Rule 2
    Now let's observe the 2d rule: If the volume price in column E is stronger than 500, that telephone is pink. accomplish that as follows:

  • select E5:E9.
  • Repeats steps 2 through four.
  • Enter the expression=$E5>500
  • click structure.
  • click on Fill, select red, and click on adequate. figure C indicates the expression and format.
  • click on ok to peer the applied rule. The values in E5 and E8 are improved than 500, so Excel applies the purple fill structure (determine C).
  • figure C 
    excelconditionalformatccombined.jpg
    to this point, the results doubtless meet expectations. Rule 3
    Now let's have a look at what occurs if you happen to apply the third rule: If the quantity price in column E is better than 100, that mobile is green. certainly, you expect Excel to follow a eco-friendly fill color to E6. to peer what in reality occurs, do the following:

  • choose E5:E9.
  • Repeat steps 2 via 4 (from the rule one section).
  • Enter the following expression=$E5>a hundred
  • click on format.
  • click Fill, select eco-friendly, and click good enough. figure D shows the expression and layout.
  • click adequate to look the applied rule. most likely, the results, additionally shown in determine D, are not what you have been expecting.
  • determine D 
    excelconditionalformatdcombined.jpg
    had been you expecting this?
    The third rule voids the primary and second rule for some values because of the conflicts that arise between the conditions. The values in E4, E5, and E8 satisfy suggestions 2 and 3—any price it is better than 500 will also be greater than one hundred. here's how Excel applies distinctive rules when they return real for the same cost:

  • When applying diverse codecs, Excel applies all formats as a result of there's no conflict between the formats themselves.
  • When making use of the same layout, Excel applies the guideline (format) with the maximum priority.

  • because each guidelines are applying the same layout, the fill color, Excel applies the rule with the maximum priority. At this point, you are doubtless questioning which rule has the optimum priority. To determine precedence, do right here:

  • choose the records set: C4:E9.
  • On the home tab, click on Conditional Formatting within the styles community.
  • opt for manipulate rules.

  • the rule on the appropriate of the listing has the highest precedence. each subsequent rule is subordinate to those above it and has priority over these beneath it. As that you may see in figure E, the guideline that applies the eco-friendly fill color has a higher priority than the guideline that applies the pink fill color. both of these rules have a stronger priority over the rule that applies the blue fill colour to the entire row.
    figure E 
    excelconditionalformate.jpg
    This dialog shows the present rule priority. fix 1: altering the order
    a method to fix the current conflict is to trade the order of precedence with the aid of relocating the crimson fill colour rule up. readily spotlight the guideline and click the Up button, as proven in figure F, and then click adequate.
    determine F 
    excelconditionalformatf.jpg
    trade the priority of both better-than suggestions.
    As that you would be able to see, altering the precedence fixes the paradox between both more suitable-than suggestions. altering the order clarifies what you suggest—practice the eco-friendly fill color when the cost is improved than a hundred and less than 500. At this aspect, that you could change the values and Excel will follow these guidelines as you meant.
    in case you do not need Excel to trade the blue fill color to crimson or eco-friendly even with the price in column E, circulate that rule to the good of the listing. in any other case, both enhanced-than guidelines will at all times take priority over the blue row fill. during this case, there is no logical error between the rules, so it's strictly a count number of what you need to see. there isn't any flaw—it's your option.
    sadly, an easy order alternate might not always solidify your intentions. You may want a extra precise software.
    fix 2: A actual expression
    altering the order mounted the issues, but some problems can be averted from the get-go by using actual expression. Expressions will also be complex, and infrequently you'll need each expression precision and order. in the case of our example, priority between the red and eco-friendly fill layout no longer concerns in case you exchange the green fill color conditional expression to
    =AND($E5>a hundred,$E5<500)
    This extra exact expression eliminates the ambiguity between both rules as a result of they now not overlap. Excel applies the green fill color simplest when the value is superior than one hundred and fewer than 500. precedence does nonetheless count regarding the blue-fill rule and how you deal with it will be as much as you.
    Precision formats
    Conditional formatting is potent, but that you can stay away from frustration by way of gaining knowledge of how and when Excel evaluates guidelines. The interface isn't specifically intuitive, however it does can help you edit suggestions and change precedence. in case you examine the cease If proper choice for any rule, Excel stops evaluating suggestions when that rule evaluates to real. be cautious how you implement this alternative!

    Learn Advance Statistics Training in Excel

    Wednesday, July 12, 2017

    creating a simple Joyplot in Excel – Tutorial


    Over on Twitter, I got here across this fascinating chart, aptly titled – Joyplot. it is the type of chart that makes you all curious and awed. So I did what any Excel nerd would do. Recreated it in Excel of direction. This submit takes you via the method.
    top time for activities and amusement #dataviz. About time for a joyplot; might do a write-up on them. #rstats code at https://t.co/Q2AgW068Wa pic.twitter.com/SVT6pkB2hB
    — Henrik Lindberg (@hnrklndbrg) July 8, 2017
    First let me share the final outcomes.
    Joyplot in Excel – peak time of the day for activities and leisure
    right here is the final overlapped area chart with just a little of formatting thrown in. it is an attractive close imitation of Henrik’s customary chart. click on it to amplify.
    joyplot-in-excel
    creating Joyplot in Excel – Tutorial
    As that you may wager, the chart is a simply an overlapped area chart (ie each and every area sits behind another, in contrast to stacked enviornment chart where they are umm, neatly, stacked!)
    Let’s delivery with a glance at facts. Henrik’s original statistics has 10,656 rows, every row containing recreation identify, time and p value – how a whole lot survey respondents enjoyed [@activity] at the moment.
    here's a photo of first few rows.
    joyplot-data
    Scrubbing and re-arranging the records
    As you can see, whereas this structure is brilliant for storing, it is very tedious if we wish to make one chart with all sequence. So let’s scrub.

  • We need to determine if an endeavor may still be included or now not. i am using the equal standards as Henrik’s. Exclude actions with p value less than 0.003 or endeavor title “taking part in activities n.e.c. *”  (no longer in different places categorised)
  • To do this, we first pivot the information on pastime and max(p). Then filter this pivot two ways – max(p) >=0.003 and label no longer equal  enjoying sports n.e.c. * Tip: You may wish to allow dissimilar filters per field within the box settings of row labels.
  • we will emerge as with 28 actions.
  • Then add a helper column to common desk that looks up the pivot and tells if an undertaking should still be protected or no longer
  • Add two more columns to fashioned table to tell height time and modified time. this can support us in rearranging and sorting the facts. Modified time just strikes time by means of three hours (Henrik’s chart is plotted from 3AM to 3AM). At this stage our facts feels like this:joyplot-data-extra-columns
  • Now, pivot the facts once once again. This time,
  • exclude activities by using record filter on consist of? column.
  • set up top and endeavor in row labels enviornment, modified time in column labels enviornment and p in values enviornment.
  • organize the report in tabular structure, flip off sub-totals.
  • We get this:rearranged-data-with-pivot-joyplot
  • Calculate normalized values by means of dividing every p price with maximum p value for that pastime. we are able to use a further latitude of 28×288 cells to do this. We get this:normalized-values
  • The next 2 steps can also look puzzling. it will turn into clear once you appear at the charts.
  • outline an offset price. beginning with 0.5. which you can alternate this later. In a separate 28×288 phone range, calculate gaps by way of multiplying offset with place of an recreation. some thing like this:gaps-joyplot
  • Now, at last calculate activity + hole values by way of adding up respective cells in every of the 28×288 tiers. We get this:actual-plus-gap-values-joyplot

  • At this stage, our statistics is a shape ready for visualizing.
    creating and formatting overlapped enviornment chart
    The chart creation process has 5 steps.

  • choose the 28×288 latitude of cells created in step 7 and insert an overlapped enviornment chart.
  • Now, reproduction the gaps latitude (created in step 6 above) and paste them on to enviornment chart as new collection (just ctrl+c your statistics and select the chart, press ctrl+v)
  • alter the order of sequence so that every pastime is sandwiched by accurately named hole sequence
  • Tip: adjusting fifty six series is painful with the chart select records > stream sequence up/down buttons. in its place, simply select the collection, analyze system bar. The series formulation has final parameter as order. alternate this number. it is handy to determine the number once you try a couple of.
  • exchange all gap collection fill colour to white. This straight creates the floating enviornment chart impact.
  • alternate the colorations of undertaking sequence. follow white / off-white border to those sequence. Your joyplot is equipped.

  • short overview of the chart advent manner:
    Let’s assess the outcome of every these 5 steps with a smaller dataset so that you can see how everything matches collectively. right here is the records for this example:
    sample-data-for-chart-tutorial-joyplot

  • Create an overlapped enviornment chart with activity+gaps information. We get this:sample-joyplot-step-1
  • Add gaps as new series to chart. You get this:sample-joyplot-step-2
  • movement the hole series in order that they sandwich undertaking sequence. Use Chart records > movement sequence up/down buttons or collection componentssample-joyplot-step-3
  • observe white color fill formatting for gap sequence. This creates floating area chart effect as below:sample-joyplot-step-4
  • finally, format the chart by way of practice some colours and border formatting and so on.sample-joyplot-step-5

  • So there you go. The final outcomes does appear happy.
    joyplot-in-excel
    alternate options to Joyplot
    while joyplot is remarkable, it is not handy to make. happily, there are a number of less complicated alternatives that we can whip up in Excel as quickly as you've got both the pivot or normalized values.  under I even have shown two such examples. read about sparklines or conditional formatting heatmaps for more.
    Joyplot choice – the usage of sparklines:
    Tip: to get axis for your sparkline, simply class the instances separated by a single house. Then go to layout phone (ctrl+1) and set horizontal alignment to dispensed. Viola, Excel will fill the cell by adjusting areas.
    joyplot-alternative-sparklines
    Joyplot choice – Conditional Formatting Heatmap 
    joyplot-alternative-heatmap
    download Joyplot Workbook
    click here to download Joyplot Excel workbook. determine the records scrubbing formulas, pivot and chart settings to learn how here is created.
    if you're typical with R, then go thru Henrik’s R code. it's much shorter than the Excel gymnastics we did with circular pivot table referencing. That noted, one of the most statistics re-association may well be executed with same ease in vigour query too.
    Your ideas on Joyplot?
    The best step we missed in Excel implementation is relocating typical smoothing of the enviornment charts. It will also be without problems delivered as a step between 3 and four in statistics stage.
    How do you like Joyplot? Would you create something like this for your company / very own records? Share your reports and ideas in the feedback section.

    Sunday, July 9, 2017

    17 Amazing Integrations to Automate Documentation with Zapier

    As consumer demands become more unique and varied, in order to keep up with big businesses, small businesses must change their back-end operations. With the accessibility and affordability of automated software solutions, this demand is not only possible, but it also saves time and money for small business owners.
    Where do you start? That’s easy! Zapier and WebMerge.
    Zapier provides businesses with the ability to sync any application they want. Based on the uniqueness of your operations, you could use Zapier to share new Tweets on Slack, send RSS feeds to Buffer, and more. With Zapier and WebMerge’s powers combined, you can automate your documentation process and eventually… rule the world!
    Here are 20 ways you can use Zapier and WebMerge to automate your business:
    1. Create and Sign Real Estate Contracts with CudaSign
      Real Estate agents are always on the go.  From meeting with new clients, running around town showing homes, to closings, real estate agents are rarely in the office. Here’s how you can create quick contracts and save some energy.
    2. Create Contracts and Agreements from Nutshell CRM
      Managing your contacts, leads, and opportunities can be a daunting task, but with choosing the right CRM can be a life saver.  Nutshell is a great CRM and in combination with WebMerge you can save your sales team countless hours.  In this example we’re going to show you how you can create a contract from a lead in Nutshell and automatically send it to your customer.
    3. Generate Contracts from Opportunities in TrackVia
      Managing your workflow can be a daunting task, but with tools like TrackVia, you can keep track of everything from start to finish – all within a single platform.  From managing a projects to collecting data in the field, TrackVia is a great solution.
    4. Send a Letter of Engagement from Clio
      Document generation in the legal world is a must-have.  By auto-populating letters, contracts, and other types of legal documents, attorneys and paralegals can save countless hours.  Another way lawyers can save time (and their sanity) is by using a practice management system like Clio.
    5. Populate a PDF Receipt from a Formidable Order Form
      Formidable is a popular Wordpress form plugin for creating contact forms, order forms, registration forms and many more types of web forms.  But what if you want to take that information collected on the form and populate a Word document or PDF?  Your options are very limited.  Unless you haveWebMerge, of course! 
    6. Send an Order Invoice from Smartsheet
      Smartsheet is a pretty cool tool that allows you to easily collect data from customers and team members, plus offering an easy platform to manage that information.  It’s like Excel, but on steroids.  In this example, we’re going to show you how to send an invoice created with WebMerge directly from Smartsheet as a new order is entered into our sheet.
    7. Create and Send an NDA from Zoho CRM
      Keeping track of all your customer information can be difficult, but getting data out of your CRM can be an even bigger hassle.  Most often you end up re-typing information into other systems, documents, etc.  With Zoho CRM you can easily capture your leads, plus manage your customers, partners, and opportunities.
    8. Send Customized Invoices for Magento Orders
      Let’s face it – e-commerce is here to stay and is an important part of any business.  Today there are many services out there that can help you build and maintain an online store front without the need of expensive coding or customization.  One of the most popular services is called Magento and they offer a product that can fit your every need.
    9. Send Welcome Letters to Hubspot Form Leads
      You’ve jumped into the world of HubSpot marketing and sales tools, collecting all of this data on your website visitors, leads, and customers.  But, you don’t have an easy way to populate documents (letters, contracts, offers, etc) from that customer data.  Until now!  With the help of WebMerge (via Zapier) you can take your HubSpot data and populate documents on the fly.
    10. Create PDFs and Reports from Knack
      If you’re looking for an easy way to build an online database application without the need for programming, you need to check out Knack.  With Knack you can get rid of those clunky spreadsheets and build easy to use web applications such as a custom CRM, proposal tracker, order management, and more.  Knack allows you to build some pretty powerful apps and we’re going to show you how you can easily generate documents directly from Knack using WebMerge.
    11. Create Service Contracts from Batchbook
      If you’re looking for a great CRM to help you with organization and productivity, then we suggest you take a close look at Batchbook.  With Batchbook you can track your contacts, manage your deals, maintain your communication with customers, and more.  Let's take it one step further and generate documents from Batchbook using WebMerge.
    12. Create and Send Contracts from Solve CRM
      In today’s competitive landscape for the CRM space, it’s tough for services to stick out from the rest.  You have to fit the needs of specific verticals or use-cases and do that really well.  In the case of Solve CRM, they have wisely picked a deep integration with Google for Work and all the apps that come along with it.  From Calendar syncing, to Gmail integration, to managing Google Drive files, Solve integrates seamlessly.
    13. Create and Manage HR Documents from ZOHO Creator
      Managing your HR processes can be a daunting task.  From collecting new employee information, tracking time off, and handling payroll, there is a lot to keep track of and a lot of documentation to go along with it.  No sweat!  There’s a great tool called ZOHO Creator and you can build a customized HR app that allows you to manage all of your HR data across your entire business.  Then, combined with WebMerge you can generate employee contracts, insurance applications, state forms, and more.
    14. Create Custom Invoices and Letters from Harvest
      Managing projects, finances, timesheets, and deliverables can be a job in itself, but with Harvest you can make life a whole lot easier.  Harvest can track all your expenses throughout a project and make it easy to bill your customers when the project is complete.  Sounds great, right?  Well, we agree, but what if you want to customize the invoices or send letters to your new customers?  Your options are limited.
    15. Create and Send a Custom Payment Receipt from Freshbooks
      Freshbooks is one of the leading accounting solutions on the market and they do a great job of helping your manage your invoices, time, and expenses.  You can keep track of all of your customers and send invoices in minutes.  But what if you want to create a customized invoice or send a receipt to your customer when you receive payment?  Your options are very limited.
    16. Send Customized Payment Receipts from PayPal
      PayPal is a great way to get paid for things online without the hassle of gateway fees, setup charges, or merchant accounts.  You can easily integrate PayPal into your shopping cart, website, or registration processes.  However, your options are limited if you want to customize the receipt that a customer receives when they pay your through PayPal.
    17. Upload a TypeForm Medical Patient Intake Form to Box
      Have you ever thought filling out forms is a pain in the neck? TypeForm makes the experience of filling out forms fluid and fun. At the doctor’s office, filling out an intake form is often the first experience the patient has upon arriving for his or her appointment. If you have a medical practice, then you might be interested in improving your intake process. With TypeForm, in conjunction with WebMerge, you can merge the form submissions with a template document and automatically file them in your digital document storage system and/or as easily print them out as hard copies.

    Monday, April 17, 2017

    Excel Solver and Linear Programming

    This article explains the use of Excel Solver for solving equations, specially the ones involving Linear Programming. In case you have no clue a good deal about Linear programming it's solving for x, this article will give you a little insight about that as well. The focal point here is to make you aware about Excel Solver and how it can help you understand  solving x for a given equation.

    What's Solver and What Does It Do

    Excel solver is an Add-on that solves equations for you. For a given problem, Excel solver can run a considerable number of permutations and combinations to discover the very best answer for you.
    Enabling Solver in Excel
    Go to alternate options:
    1.png
    select Add-In:
    2.png
    click on 'Go' button, assess Solver Add-in, click on adequate:
    3.png
    make sure to be capable of see the Solver button for your Excel information Ribbon:
    4.pngwhat's Linear Programming
    LP is a mathematical formulation for determining a way to obtain the highest quality result (corresponding to maximum profit or lowest can charge) in a given mathematical mannequin for some list of requirements represented as linear relationships.
    Some sample problems that Solver can clear up issue 1
    as an instance we have a feature e.g. f=x2-x+2 and we are looking to find out the minimal value of x where -1<= x <= 5.
    problem 2
    an organization manufactures desks and chairs. every desk makes use of 4 instruments of wood, and each chair 3 devices of timber. A desk contributes $40 to income and a chair contributes $25. marketing restrictions require that the number of chairs produced to be at least twice the variety of desk produced. There are 20 units of wood obtainable. What set of construction will deliver optimum income.
    problem 3
    here's a extra advanced problem which is represented when it comes to equations and solved through the matrix method.
    Maximize 50x1 + 30x2 + 25x3 + 30x4
    the place
    2x1 + 2.5x2 + 3x3 + 1.8x4 = 800
    1.2x1 + 1x2 + 2x3 + 0.8x4 = 400
    1.5x1 + 1.2x2 + 1.5x3 + 0.8x4 = 380
    x2 = 50
    x3 = 30
    x1, x2, x3, x4 = 0
    solving the complications the usage of Excel Solver problem 1
    here picture indicates the solved illustration. telephone in green (C15) is the mobilephone which gets the price through solver and is named as x.
    components for cell C12 is x^2-x+2. through default, put 1 in phone C15 which is the preliminary price for variable x.
    5.png
    objective box is f which is the identify of cellphone C12 which we want to remedy through solver. "by means of changing the variable cells" box has x which is cell C15, our variable. we've also set the constraints the usage of add button x <= 5 and x>=-1. simply click the 'resolve' button and it will supply the value of x.
    6.pngdifficulty 2
    This issue is solved in a standard means. Unitstoproduce is the latitude C18:C19. totoalwoodused is the identify of cell F20.
    7.png
    right here in here determine, that you would be able to see the solver surroundings. provide 1 in eco-friendly cells as initial values. Solver should still be capable of provide the suitable values for them.
    8.pngproblem 3
    In precise existence, LP complications asre now not so simple as in problem 2. for this reason, we need to solve the difficulty the usage of the matrix. in this issue, that you would be able to see many equations which that you can relate to some equations in problem 2. to maximise the result of the equation, all we need to do is to maximise (Transposed C) * x.
    9.png
    For the sake of distinctiveness of the variable identify, I named the matrix variable as xn, bn, and so forth. Values of x is what you are looking to get through solver. give 1 as preliminary values for x1, x2, x3 and x4. for those who run the solver, it will update these eco-friendly cells with the solved values.
    10.png
    historical past

    Learn Advance Statistics Training in Excel