Sales Spreadsheet example

VisiCalc was launched in 1979 as the first spreadsheet application for home users to bring the many features of big biz to the market for everyone. This was the first use of the performance of automatic computations to help someone administer their own information. Tabular computations gave us the ability to enter, manipulate and compute everything we wanted and save it in digital form for reuse.

They could construct anything from ordinary food listings to finance schemes fed by huge datasets - the options seemed infinite. Tabular calculations were the initial killers application, one of the major motives for owning a computer. Now you can use spread sheets to create your own applications. Web-enabled spread sheets like Google Sheets let you reformat and analyse your spreadsheet information with off-the-shelf spreadsheet utilities, retrieve information from the web, and run sophisticated workflow tasksutomatically.

We' ll be learning in this section how to do exactly that by creating a fully-fledged, fully automatic Contacts Manager (or CRM) system with just one spreadsheet. Explains everything you need to know to create any part of your spreadsheet. However, sometimes it is simpler to track together with a ready-made spreadsheet. To do this, we've attached a sample form with each example in this Tutorial - all you have to do is copy it into your bankroll.

Simply open the Google Sheets templates and copy them to your own Google Drive account-click File and select Copy. Or you can take this leaf, take it apart and change it to suit your needs. Every use case is on its own separate page, so that you can see how it might work together to create a full customer service team.

However, if you have the courage, you can create your own spreadsheet from the ground up. This latter will help you really get the sense of being able to create in Google Sheets instead of just using it. The most important "aha" moment you'll experience with sheets is the realization that your spread sheets are linked to the web - meaning they can work for you on-line.

Googlesheets can automaticaly read information from other sites (something we'll look at later), and it also contains a formulas utility, GoogleForms, to collect information and store it directly in your spreadsheet. We' ve reviewed the Google Wells and Google Forms tools in Section 2 - Jump back for an update if you haven't created your own before.

Well, let's give it a try by creating a template to gather leads information from potential customers. Now you can incorporate this template into your website, adding it with a pushbutton or hyperlink on your website, or sharing a hyperlink to the template via your online community, via your online community or via e-mail. By clicking on your links, your visitors can fill in your question and the information is stored in your spreadsheetutomatically.

First of all, you should include a formula in your spreadsheet to easily collect information. Here is the example made by me - try it out and send your (falsified) information! In order to be able to implement your own forms, you first need a spreadsheet (of course). And if you didn't copy the demo sample, go to your Google Drive and click New > Google Sheets as shown in this screenshots.

As soon as you have opened a page, click on Insert > Page and a new screen will open in which you can begin to fill in your page information. In this section you can select who is permitted to see the forms and how many answers each individual is permitted to give. When you use Google Apps for Work, you can specify that only your company's own members will be accepted and their ID will be captured each time they send the application.

It is particularly useful for in-house capturing or for external contractors who capture information from associated corporate account. I' m leaving all my options empty for this formula, so you (and everyone else) can access it and get limitless answers per individual. When you create a general feedback request to facilitate your contacts with your teams, you will probably want the same setting.

Adding a question to your forms depends on the information you want to gather, so consider what kind of question you need for your own forms. Trying to gather information so that you can easily tamper with it later - ask general question with each response in a separate area.

Rather than allow a person to input a free-form response to a quiz, for example, you provide 3-5 fairly extensive text-based selections in a multiple-choice quiz. Simply input the desired quizzes, select the response category, and Google will automate the creation of lines in your spreadsheet for each quiz in your forms.

There are also some useful features you can include in the sectioned forms to personalise your answers to a user's question. Go back to Google Forms Docs 2's Google Forms Section guide if you need an update. Below are some example answers to my demonstration request forms for this tutorial: So, now you have some dates, what are you doing with them?

I will begin by renaming my Answer Sheets. Initially, the shape is displayed as a page with the name "Form Responses 1 ", but I did rename it to "Customer Data" by double-clicking on the name at the bottom of the spreadsheet. As soon as you change the name of the answer page to a more appropriate name, we may include the information in that information and insert it into another page.

Quickly refer to another leaf by using the =INDIRECT() command. Here is what the formulas would look like for the forms we just created: Client information is the location where all new forms are stored. The !! is used to refer to another spreadsheet in the same spreadsheet. A2:H is the cell area that contains the dates I want to refer to - and it will contain all the new dates that come in as well.

If you want to keep the answers separated from where you are manipulating the information, you can use the IMPORTRANGE () feature in a totally different table (document). In my example, the equation for importing the spreadsheet would look like this: Client details! A2:H refers to the area of the source file to be imported.

Once you have entered this equation, you will need to authorise the new spreadsheet to gain control over the information - simply click on the "Allow Access" icon that appears next to this one. If you click on "OK" your information should appear after a few seconds. You can now retrieve your forms delivery information from all your tables and workbooks!

I' ll be demonstrating a use case by building a basic CRM system using the just created forms. Manage your customers and contacts in a spreadsheet is a proven way of doing things. State-of-the-art datamanagement applications often speak of "ditching spreadsheets". "However, I like to use Google Sheets to manage this kind of data: it makes navigating and organising information very easy.

You should enter the following information if you have not yet installed a sales activity system: Contacts information, the name, telephone number, e-mail, business, website and profile of each individual. Custom value (single sale, life value or whatever is appropriate). You' re probably already looking at some of this information, but a granular spreadsheet program can help you save more of it in a more simple form.

By eliminating many of the disruptive functions of a full-featured CRM, Google Sheets enables a degree of customization you won't find in a standard CRM application. Don't be afraid: it's simple and a good way to practise your spreadsheets. Make sure you include a single row for all the information you want to keep an eye on.

According to my example above, I have labelled my colums as shown below: Since the " Refreshed " button is added to every input, I would like to insert this information if I want to do something with the ages of the entries in the next time. CRM is about tracking contacts and changes in the client life cycle in a way that you can retrieve later.

Previously, I showed you how to create a template to gather information from an external resource (not one of your teammates or co-workers). Our completed forms are intended for potential new clients, but they can also be used by me or a member of my staff to enter information quickly and in a way that is easy and quick.

This CRM example uses the information I enter in this CRM example as my CRM page client list. I will then allocate an ID to the client and select a member of the project staff to be allocated to the client on the basis of their future month's expenses (one datapoint on the form).

=CHOOSE () - to choose a member of the project on the basis of his/her month's expenses. =ROW() - to get the number of the line containing the information. =IF() - to specify terms when things should be done. Prior to creating the ARRAYFORMULA(), I want to make the areas in my formula easily referencable.

They don't have to do that; sometimes I choose not to call areas so that I know exactly what dates I'm looking at. In order to create and name an whole row, right-click on the grey check mark with the character at the top of the row and choose Create named area. You can do this for all your most important areas of your datas (in the picture I have only shown a large example of cells).

I' m doing this because if I ever want to sort the records and extract a truncated listing of certain records, the line number will not remain in the records between the pages or be exported to CSVs. In order to insert a customer ID, use this equation in the equation line of your ID columns (the one you will be hiding):

will return the ID basing on the line number -3 (this should compensate for the number of headers used for the headline / concealed calculation line). A " " in the third line of the equation is intended to prevent "FALSE" from being returned in any line below the last one.

It' s pure aesthetics, but I find that I can identify an empty cubicle more readily than sort through a series of "FALSE" s blended with my work. We add the following to the equation line of the "Assigned" column: To change your customers' details, do so in the Customers' Details page and not in your own Dynamics CRM. Click on the "Change customers' details" button.

The CRM worksheet uses formula to refer to the customer information in the " customer information " worksheet. Instead of modifying the real dates, if you changed the information in the CRM leaf, you would be breaking your formula! When you want to enter more information by hand, you can enter more information to the right of your last record containing information.

You can, for example, insert a "Notes" section in the K section to provide more information about each new client. Do not want to type in every single item of information you have. Instead, we place a direct hyperlink to the entry page on your CRM page. In this way you can simply click on the links to open the forms, fill in and send the information like a client.

My forms links with the HYPERLINK () feature are placed in a new line at the top of my page. In order to get started, you need the shortcut that you would like to give to someone you want to include in the table below, such as this shortcut for the sample table in this example guide.

You can find this share can be found by either selecting the Submit Forms button on the edit page of your appended forms, or by selecting Shape > Go To Live Shape from the spreadsheet icon bar and copy the form to your browser. As soon as you have the shortcut, go back to your spreadsheet, choose a box in which you want to insert the calculation, and make your box look like this (but with the address for your form): =HYPERLINK("https://docs. google...form#start=invite", is the address for the shortcut to the forms.

Just send some information about your forms and see the results in your CRM spreadsheet - you should see that the new dates are displayed almost immediately. How about a more sophisticated (and incredible great) way to gather extra information about these new prospects? The addition of some automatic scratches - the automatic copy of dates from other sites into your pages is as simple as the addition of some integrated features.

Use the IMPORT features in Google Sheets to import almost any information you want from most web-sites. importfeed( ) - Import an RSS or ATOMeed. ImpORTRANGE () - Import files from another Google Sheet. As we continue our CRM example, let's see if we can get more information about these new potential customersutomatically.

And if you haven't entered a qualifying website for your contact in any of your forms, you should either modify some addresses in your Customer Data page now, or type them in some other manually created forms with qualifying hyperlinks. For example, if you have the Facebook Planet on your website and a hyperlink to every Facebook page of your friends, Google Sheets can import any person's Facebook Planet on your website as well.

Dependent on the item you are trying to scratch (and if you want to multiply the calculation to several locations ), there are some xpadhs that are the same at most locations. An example of these default items is the Meta Tags page. They are the best choice if you want to copy files from a variety of different webpages.

We use these meta tags to gather supplemental information about the website or services of our lead (s) in order to better support the sales people we have allocated in the CRM section above. We will use here the feature =IMPORTXML() (which is by far my favourite feature IMPORT()). =IMPORTXML() makes it simple to capture similar information from different pages without exactly how it is marked or structure.

Think of it as a classic IF() we used in the example above, but instead of inserting your own conditional instruction (like "If this happens then do this"), this instruction will always be "Check to see if this form results in a Error when you run it".

You will see when a phrase "makes a mistake" when you see a #REF! This is sometimes due to an incorrect equation, but sometimes it is just the outcome of a equation that does not capture it. However, Google Sheets should tell you the cause of the issue. If you want to suppress these errors, you can wrap our calculation into the IFERROR() functions, which displays a different outcome when the cells encounter a bot.

The one thing to be aware of is that by doing this you are silently effective in silencing any useful mistakes that would inform you that your formulation is wrong. This is the filled out form for scrapping the "title" of a page: Website is the area where the web addresses are found. This is the filled out formula:

Let us show our results in the same line as our CRM contacts (instead of jumping to the next CRM instead). It takes the perpendicular information and turns it around to get into the cell to the right of it. Here you have the definitive, finale versions of the mezzotint formula:

This is how it will look in your Formelbox: In the Form and CRM section, we used ARRAYFORMULA() to automate the application of formulations to new CRM items. You MUST copy your calculation for each use of IMPORTXML() because ARRAYFORMULA() does not generate new calculations in the following lines (only uses the calculation on the next line of the range).

To do this, grab the small text field that appears at the bottom right of each cubicle containing your scrape and drag it over the new lines of scrape objects. When you reach your fiftieth point, what happens? When you try to insert normally, you will insert the calculation only into each row (which would definitely cross your 50 IMPORTXML() limit) - but inserting the value just retains the text you have already pasted from the Sites.

While you shouldn't see that anything changes when you insert one of these boxes, you will find that if you double-click on one of these boxes, the calculation is gone and only the text information is left. You can use this procedure if you want to capture information that has been tampered with by any of the formulas in your page and insert it into another page or another spreadsheet or documents.

Do you think it might be difficult for you to know where the "last worn contact" was? Using this gimmick, you can emphasize any kind of information in your page. In order to begin, there is a grey square in the upper leftside of your page, which is neither a line nor a colum.

When you click it, EVERYTHING on the page is highlighted. Right-clicking this field selects both the whole page as the selection area and opens the Conditional Formatting side bar on the right. I am using the "Custom Formula is" at the bottom of the first dropdown menu.

Into the calculation field we put this formula: =AND( return either TRUE or FALSE from both logical instructions. $C:$C"""""" to ensure that there is a CRM record by looking at the Name columns. $K:$K="""" verifies whether this user has deleted his metainformation by looking at the first colum in the metainformation colums.

You' ll find that we had to use the real columns and dollars $ characters in the Custom Formatting equation in the equation. A final thing to be added is a columns that is used to easily know if our agents has made contacts. Make a slit and mark it as you like (mine is "Contacted").

You can then choose how you want to "check off" when the client has been called. Here is what some sample dates may look like in this field: Now we have entered a leads or customers, allocated them to a sales rep and provided them with some extra information. We' ve also added a feature to highlight new items so we know we need to scratch them immediately when we download our CSR and have given our staff a way to track whether they have contacts.

So why don't we just put some community and public relations on tweet? We captured the Twitter handles of each potential customer at the point of entering information in the forms and CRM samples above. Let's say everyone completes their handles properly (and everyone has a Twitter account) and sets up a Twitter tracking tools that accompanies our CRM.

Well, this leaf will be able to You don't have to use every datapoint from the CRM blade. In this example for socially minded we use the designated areas, which are added (date added), name (customer name), tweet ers (@handle) and allocated (the member of the teammate who will contact us). We' ll have to attack every area with a single equation.

First we import the areas into the Social Media sheet: The name: =ARRAYFORMULA(IF(twitter>""""", verifies that the person has a handling in the CRM and applies to all further lines. name,"""") will return the name of the person if the IF() was real, otherwise it will return """. Tweet: Provides the specified area "Twitter".

Similar to the Name columns, except that it will return the associated area that was the member of the teammate and not the name of the person. This is how your "Social Media" page will look at this point: We need a tweet with the CRM base import into which we can add the information.

Divide your tweet into either your columns heading line or the special calculation line (the one marked red) into segments that enclose your import name, tweet and associated datas. For this example, I dismantle it as follows: Tweet Copy" is the default setting. This is the filled formula: =ARRAYFORMULA(IF(twitter>"""""" verifies if the client has a tweitter handler in the files and applies the equation to all lines in the next. Turn, shift, and J$2&name, and L$2&assigned, and N$2 combine each of the CRM datapoints with the tweet we create in the equation line.

Here is an example of what you get: Because we manage client information from our CRM sheet, it makes good business sense for us to be able to interact with the client directly from the spreadsheet. This can be achieved by using a Twitter "Intent" URL that allows you to prefill a tweet with a character text strand in the URL.

The Twitter intention URL is built like this: www. twitter.com text= + everything you want to say! Anywhere you want, you can use this link to quickly tweet without having to jump to Twitter. This example will break the Twitter URL with another HYPERLINK() feature (just like the "Add New" CRM customer).

This would be the entire formula: =ARRAYFORMULA(IF(twitter>"""", verifies if the client has a tweeting in the CRM-page. HYPERLINK ("https://twitter.com/intent/tweet? text= "&E2:E, inserts our finished dynamical tiles in colum er A at the end of the Intent HTML thread. "will set "Tweet This" as text to be displayed in the cells, and "" is what will be return if there is no Tweitter email address for this line.

And one of the most frustrating things about trying to automatize a Twitter quest is the limitation of your personality. Particularly if you generate a tweet this way in a dynamic way, you will want to know how many signs your a tweet contains so that you can try to take into consideration the variable nature of the entry to it.

Start by defining the Tweet Copy columns as "Tweets" using the Right Click > Set Named Area feature, with the D2:D lines emphasized. This is the equation for calculating the length of our finished dynamical tweets: A tweet is a great way to remember your friend about the call, but an e-mail is an even better way to track the call and get back to it intensively.

Let us use our spreadsheet to e-mail our sales representatives to our contact details on the third trading Day after they contact us. If the contact has been reached, please allow 3 workingdays for the contact to contact you and e-mail them. We use a multi-tiered tap that searches Google Sheets for a new listing, then maintains it for 3 business days to create the e-mail.

As a first measure we want to get our colum "Contacted" via an ARRAYFORMULA() into our Social Media Blatt. If this is the case, =ARRAYFORMULA(contacted) will import the specified area from the CRM worksheet. So, please redo this process and insert another new field with =ARRAYFORMULA(email) to include the e-mail addresses of each member.

As soon as you have an overview of the user surface, start a new Zap, use Google Sheets as your triggers application and chose Update Spreadsheet Row as your triggers. Now, you need to link your Google Sheets accounts if you haven't already done so. Next, please pick the table and spreadsheet you want to view - in this case, pick your own dynamic table and social media page.

Then you can specify which columns should be monitored for changes, and we want to monitor the Contacts for them. Then click the + symbol next to the To box and choose your e-mail pillar in Google Sheets. The name of your friend can be reinstated in the human form by using the + symbol on the right to copy the Google Sheet box and paste any other personalization.

Then you are done: When your sales force flags someone as "contacted", the Zap notices the value of your value in the " "contacted"" field and sets your e-mail to send in 3 workdays. Use what you've learnt in this Tutorial to build your own Google Sheets system. No matter whether you're trying to substitute an old work flow or just can't find a toolset that does exactly what you need, Google Sheets is agile enough for the task.

Spreadscraping and the addition of information with form can build some high-performance yet easy-to-use applications to help you organize your contact list or anything else you want. First, we'll take a page break and look at some of the best Google Sheets hiding functions for reading, pasting and converting text into contours, HTML spreadsheets and more, so you can build even more sophisticated applications.