Friday, November 4, 2016

Show and hide All formulas On An Excel Worksheet

Dear Friday Excellers, let’s analyze a terrific method to promptly find all of our formulas on an Excel worksheet and conceal them. Todays’s post is in response to a question i was asked recently, as a colleague crucial to locate all the cells on a (large…very big) Excel worksheet and hide them, so the worksheet may well be disbursed with out fear of all of their challenging work on their formulas being undone, with the click on of an end clients (clumsy) mouse.
I do myelf regularly conceal formulation, there are just a few reasons- it can be that I either don't want different users to look the formulas, or I don't want users to alternate them in any way either maliciously or accidentally. So, right here is a brief and productive solution to determine the cells with formulation and conceal them. There are a couple of (effortless) steps to it.
Step 1.



  • select your entire data in your worksheet.
  • Hit F5
  • click special
  • opt for formulas

  • find-all-formulas-on-a-worksheet
    This selects all of the cells in your worksheet that comprise a formulation. within the example i'm the usage of, I even have formulation running in a couple of areas, and they're all highlighted.
    find-all-formulas-on-a-worksheet1
    Step 2.
    We now need to disguise the formulas. All Excel cells have two attributes, that of locked and hidden. Locked cells are just that, locked and can't be modified with the aid of an end person, whereas the hidden attribute potential that formulation contained in these cells are usually not displayed within the method bar on the desirable of the worksheet.
    We do although should also remember that none of these attributes are in force except a worksheet is covered. So, onto to the element of Step 2. We already have our cells with formulation selected –



  • home Tab – Cells neighborhood
  • layout – coverage – offer protection to Sheet
  • ensure that offer protection to Worksheet and Contents are chosen
  • Enter a Password when promtped
  • Job completed!

  • Now, if we click or choose probably the most cells that contained a method, see if you can see your Excel formulation now?…..nope…idea now not……!
    find-all-formulas-on-a-worksheet2
    How do YOU prevent users seeing or gaining access to your Excel formulation? pls share below.

    Books Recommended

    Wednesday, November 2, 2016

    How to get rid of (blank) Values in your Excel Pivot Table


    You could call it perfectionism, however I hate when my reviews don’t appear their most effective. one of the most traumatic points is the “(clean)” text you can also have for your Excel pivot tables.Jeremy_Cottino_Blank_values_figure_1
    I’ve tried some pivot desk alternate options to get rid of that notice, “blank,” but nothing appears to work thoroughly.
    Jeremy_Cottino_Blank_values_figure_2
    So I’ve get a hold of another strategy to eliminate those blank values in my tables. It requires playing with conditional formatting.



  • choose the cells you need to get rid of that demonstrate (blank) textual content. It is usually a single telephone, a column, a row, a full sheet or a pivot desk.
  • On the domestic tab, go on Conditional Formatting, and click on New rule…

  • Jeremy_Cottino_Blank_values_figure_3



  • select format handiest cells that comprise.
  • Set that rule class to mobile price | equal to | and enter (blank) textual content.

  • Jeremy_Cottino_Blank_values_figure_4



  • click on on structure…
  • On the quantity tab, select custom category, then class three semi-colons: ;;;

  • Jeremy_Cottino_Blank_values_figure_5
    I should admit, it’s a sort of magic.
    Jeremy_Cottino_Blank_values_figure_6
    A edition of this article first looked on Jeremy Cottino’s blog.
    graphic supply
    Written by way of Jeremy Cottino
    Jeremy Cottino, PMP, ITIL certified, is a senior assignment supervisor with eight years of experience in venture planning/scheduling and management of tremendous IS/IT tasks for multinational companies. He’s additionally a expertise enthusiast and Microsoft undertaking MVP. discuss with Jeremy’s weblog or electronic mail him at jcottino@hotmail.com.

    Related Books

    Tuesday, November 1, 2016

    Excel Regression: What determines the fee of games?

    this text can be my first of many makes an attempt to clarify the conduct of Steam's markets and neighborhood. The difficulty tackled today is considered one of online game pricing. have you ever questioned how the fees of games form? absolutely, each online game has its own set of characteristics and its own set of patrons forming demand, youngsters greater standard tendencies will also be identified. if in case you have heard some of Gabe Newell's speeches you'll know he often mentions interesting records that Steam has centered through analysis. as an example, if a video game is made free-to-play, on regular its energetic participant count number increases tenfold and income from connected items/functions triples, when compared to an analogous online game with a standard price arrangement. this is then a nice deal, as the marginal charge of distributing each and every next reproduction of a video game is bubcus these days.
    The goal of this article is to discover a characteristic that determines the fees of games, particularly multiplayer games. Now, you yourself can form some ideas of what quantifiable features are value due to the fact that and consider free to share them within the feedback. i'll give one decent illustration right here.
    leading bit
    one of the vital key aspects of a multiplayer video game is actually the gamers taking part in it. a bigger quantity of active players and greater different group drastically raises the perceived value of a multiplayer online game. furthermore, it reassures the purchaser that the video game is smartly acquired and frequently loved by way of others. a greater variety of servers, extra mods and neighborhood content is also an added bonus.
    My speculation is for this reason, that the amount of lively players (and implied group dimension) of a given game positively influences the present cost of the video game.
    To examine this relation, i will be able to use a sample of 15 generic Steam video games and think about their fees, as well as volume of lively avid gamers on the time of statement (September 15th round 19:00 Amsterdam time). The initial part of the evaluation should be primary ample - at first, I calculate the expense per every thousand of energetic gamers. This gives a brief estimate for the way plenty every game "charges" for the improvement of getting extra playmates. a normal of 2,1 Eur/1k lively players is estimated. This lets us form the final column, which just compares each games ratio to the commonplace. right here we are able to form a rough conclusion of whether a online game is "overpriced" or "underpriced" in terms of how an awful lot you pay for becoming a member of a group.

    So what do we learn from this? neatly. The price/lively avid gamers ratio varies fairly somewhat over these games. Ark expenses 28 Eur and has a whopping 38k active avid gamers, so the ensuing ratio is just 70 cents per each and every 1000 lively gamers. One must agree with, however that Ark is a beta, which explains a decrease expense to enter the neighborhood.
    Age of Empires, nevertheless has a modest player count of 3,6 thousand, but fees a fortune - 20 Eur for a online game it's fairly a whole lot from the ninety's... The clarification right here is that AoE2 has a loyal client base it truly is inclined to pay excessive premiums.
    So there is a little bit we will collect right here. however now let's do something extra fancy. Lets plot the "lively player count number" on the X axis and retail fee on the Y axis. Given the hypothesis, i am hoping to peer a nice neat line of dots. What i am expecting besides the fact that children, is nothing like that, because the volume of lively players is on no account the handiest variable contributing to the price. not to mention, every video game has its personal pleasing facets that certainly overstep any ceteris paribus assumptions which are fundamental for seeing the actual remoted effect on the expense.
    smartly. not a extremely neat line, in case you dont count number the black trendline that I drew. So what do we see here..?
    smartly, first off, the R^2 value means that, during this pattern, sixty eight% of the variation in fee does fit the trendline. here's now not a really professional statistic for finding out the models legitimacy, but for simplicity, we will take what we can.
    Assuming there is some fact to this trendline, we have estimated that a multiplayer video game without a energetic gamers is expected to charge 13 Eur. youngsters, with each and every subsequent one thousand of energetic players, the fee patrons are willing to pay raises via around 0,8 Eur.
    here is a neat desk with the consequences.
    enjoyable right? smartly, lets go yet another step. intuition most likely tells us that the size of the group handiest concerns to a definite point. In different phrases, if there are 2 different gamers, you'll doubtless not need to play that a lot. If there were a thousand, you can be more interested. At 2000 you would be fairly satisfied and get some group advantages, but at some point you might have already got satisfactory competencies pals, foes, servers, mods and forums to move round, so the measurement of the group would now not remember that lots anymore.
    So how do we look at various his? smartly, here's what I propose. Lets straight away make a further mannequin, only now lets use the 10 least common games from the record. The "essential community dimension" estimate will then be 25k energetic gamers. After 25k, we assume that the critical measurement is reached and adding extra players doesn't enormously affect the games cost. we can estimate the identical model for games which have a smaller active participant count.
    here is the graphic for the small multiplayer games.
    the road seems to seem decent, although, one must note that less conventional video games are priced rather in a similar way, in addition to run discounts, with 19,99 Eur being a favored quantity.
    but what do the numbers say?
    smartly, firstly, the R^2 isnt unbelievable - 50% of deviation suits the mannequin. despite the fact, we are able to see that less generic video games have a reduce anticipated cost with no gamers taking part in it - simplest 10,5 Eur, in comparison to 13, when 5 greater time-honored video games have been included. This tells us that the "consistent top class" game enthusiasts are inclined to pay is lower for untested and unpopular games. Makes feel appropriate?
    The marginal cost boost per lively participant count number, youngsters is a great deal larger. people are willing to pay 1,1 Eur for each and every subsequent one thousand players, compared to 0,8 when all video games have been regarded. So the marginal advantage of further gamers is larger for video games which are less regular. also corresponds with our intuition.
    Now. here is a little issue I need to admit. Ten video games is way too little statistics and two Excel regressions are a ways too little evaluation to prove these relations, however we are able to certainly kind some figuring out from this.
    What have we learned?
    The analysis means that there is cause to consider active participant count number as one of the vital variables forming the rate of multiplayer games. furthermore, the marginal effect is precisely wonderful for video games with very small communities and reduces because the neighborhood grows bigger. Crucially, despite the fact this analysis is removed from mighty and considers a slim set of games. The alterations in contribution of every active player to the communities cost may still also be regarded, as further players are important for some video games and complementary for others. a cost of congestion, if active player count number is simply too high or poorly managed should still even be regarded. moreover, additional analysis may still be achieved on a improved sample and with more explanatory variables, to form a more actual model.
    Hope you loved the article and discovered a little bit about financial reasoning and the video video game industry.  leave a remark together with your recommendations!

    Related Books

    Regression Analysis Microsoft Excel

    Regression Modeling Strategies: With Applications to Linear Models, Logistic Regression, and Survival Analysis (Springer Series in Statistics)

    Microsoft Excel Data Analysis and Business Modeling (5th Edition)

    Wednesday, October 26, 2016

    How to Use an Excel Pivot table for facts evaluation

    The pivot table is without doubt one of the single most powerful tools within the Excel 2013 repertoire 5 Microsoft notice & Excel talents You ought to ought to succeed on the workplace 5 Microsoft word & Excel advantage You ought to should be triumphant at the workplace "Get that file to me, ASAP!" A core set of Microsoft Excel and notice tricks could make this task less demanding and help you whiz up a trendy report in no time. read greater . it's often used for significant statistics evaluation.
    you could use the pivot table as an interactive facts summarisation device to automatically condense big datasets right into a separate, concise desk. that you may use it to create an informative abstract of a big dataset or make regional comparisons between brand revenue.
    Pivot tables will not have to be intimidating – far from it – and we are here to help you release one of the true hidden vigor tools of Excel.
    To display the vigor of pivot tables, we should be the usage of the uk govt dataset 2013 UK Fleet Landings by means of ICES Rectangle which can also be downloaded by way of the linked web page, if you are looking to play alongside at domestic.
    Summarise your entire facts – Small Vs. significant Datasets
    how you organise and community your facts could make vital changes in uncovering developments What's Trending? find out With These actual-Time statistics web sites What's Trending? discover With These true-Time facts websites it's astonishing how at once suggestions can spread from person to adult at the moment because of the cyber web and social media. Is it viable to keep up? The brief answer is sure -- with a catch. study extra . In a small pattern, as in our instance beneath, it's convenient to spot developments:

  • administrators are paid more
  • creative administrators have lowest standard wage
  • put up-grads earn, on usual, £9,000 greater than individuals with a school education

  • Example Excel 2013 Worksheet
    Scaling up to a government dataset demands a special strategy. What seems to be a big listing of uncooked, unlinked statistics can in fact hold the important thing to enterprise success or failure. Pivot tables are proper to higher datasets and to work truly well, they should hit just a few simple criteria:

  • Numeric assistance: Your subtotals require this facts.
  • Duplicates: as a minimum one column with replica records stages. duplicate facts degrees enable us to greater readily filter and analyse our information, proposing numerical facts ranges for the duplicate sources.
  • Import uncooked facts into a Pivot table
    thankfully for us, Excel 2013 contains the most up-to-date pivot table tool, now even providing up advised pivot tables throughout the full spectrum of your accessible dataset. besides the fact that children, the place the presets do not fit the invoice, which you can all the time manually choose the pivot table wizard to get you rolling:
    Pivot Table Insert
    Clicking ‘Pivot table’ should then carry you to this menu:
    Create Pivot Table Options
    keep on with the existing settings for now, even though as that you would be able to see there are options to join an external information source (i.e. MS access), or location the pivot table in the existing worksheet. For the latter alternative be certain to investigate that your new pivot table will no longer obscure or ruin your present data (even though that you would be able to all the time hit CTRL+Z Oops, I Did It again: 7 Keyboard Shortcuts users preserve Hitting by way of Mistake Oops, I Did It once more: 7 Keyboard Shortcuts users retain Hitting by way of Mistake It occurs even to probably the most experienced of clients: the unintentional keyboard shortcuts. No count number how commonly you use your computer, bizarre issues can beginning happening if you’re going too quickly, should you lean on... read greater if something bad does occur!).
    So, clicking ok should present you with a completely new worksheet for your manufacturer spanking new pivot table. Excited yet? remember to be: here is where the records magic happens!
    Pivot Table Excel 2013
    when you are nevertheless with me, and that i really hope you're, your page should appear to be what's proven within the screenshot above, or whatever very similar if you selected a unique Worksheet. Excel has very kindly sorted our Pivot table fields so as we will work in two techniques:

  • Drag and Drop: The pivot table fields listed on the right-hand facet will also be dragged into the four areas beneath (Filters, Columns, Rows and Values). that you would be able to opt for specific information gadgets that you simply wish to go-reference towards any variety of differentials.
  • Add to document: Clicking individual dataset fields will add them at once to the ready report desk (PivotTable2 in screenshot above). This components can also be used to swiftly construct, analyse, collate and adjust the obtainable statistics.

  • while both methods bring an identical results, Add to record lets you with no trouble opt for your favourite statistics container and wonder in wonder as your file table is converted. As a bonus, each time we click a brand new facts container it's automatically delivered to the enviornment Excel deems suitable, while reminding us that we cannot add a data ‘container with more than 16384 items to the column area.’ Thanks, Excel, always looking out for the company grownup, aren’t you?
    decide on & examine facts Fields
    Now, click on Vessel Nationality, Species identify, Landed Weight (tonnes) and value (£). Your information subtotals should still now seem like this:
    pivot row examples
    whilst your pivot table fields should suit this:
    pivotrowexample2
    we can now hastily glance at our custom pivot table information fields and analyse the available statistics to confirm any developments. because of our actions, we now comprehend that England fishes essentially the most Bass during the united kingdom, although the fish itself is value extra in Wales. perception gained!
    Filtering Your records
    consider free at this point to play around with the distinct fields, gain knowledge of which types of facts produce concise correlation, which statistics fields want tweaking to display their secrets and techniques and to comprehend the cost actions of filter settings:


  • Hover over the preferred records container, notice the small black arrowhead to the correct of the text
  • making a choice on the black arrowhead displays a drop down ‘Filter’ menu – extremely helpful for setting apart statistics inside a single box, or comparisons between an identical records levels throughout distinct facts sources.
  • choose the Vessel Nationality filter menu.
  • by using default, all information ranges are chosen. Unselect all (with the aid of hitting choose all), followed by using reselecting England and Scotland. This isolates the records for these countries, filling our pivot table with concise, comparative records we are able to begin to analyse for advantage developments.

  • Pivot Table Filters
    modifying the filters of each available box will almost always directly alter the information introduced within the pivot table and is a certain fireplace system of unlocking the authentic capabilities of your information Do Visionary net analysis experiences the use of Deep net information & Excel web Queries Do Visionary internet analysis experiences using Deep web information & Excel web Queries What would you say if I informed you that you have the tools at your disposal to do floor-breaking, Earth-shattering analysis? smartly, you do, and i'll demonstrate you ways. study extra .
    size matters
    For this example, we used a small share of a relatively gigantic dataset. Pivot tables work top-rated when they have several thousand particular person data, dissimilar data fields and various reproduction numerical figures. That is not to assert their relevance is precisely associated with gigantic datasets however, when coping with a enormous quantity of uncooked information, nothing can beat the pivot table in ease of use, statistics filtering or its concise summarising amenities.
    Has this ‘how to’ helped you, or have we overlooked whatever thing? We’d want to hear your pivot table experiences below!

    Related Tools

    Master Excel Pivot Tables!

    Excel Video Training Course - Beginner & Advanced

    Excel 2013 Pivot Table Data Crunching (MrExcel Library)
    Excel PivotTables and PivotCharts: Your visual blueprint for creating dynamic spreadsheets

    Monday, October 24, 2016

    How to Create an Excel Pivot table With Medians

    excel_featured_photo.jpg
    As a marketer, you already recognize that Microsoft Excel is a magnificent tool for sorting, inspecting, and sharing information. difficulty is, one of the vital most really helpful formulas are in fact hard to determine — even for us information-crunchers. 
    as an instance, we’ve walked during the steps of the way to create a pivot table before, however alas pivot tables don’t compute median values, which can also be incredibly effective assistance with which businesses can analyze their increase.
    fortunately, there's a workaround in Excel referred to as the array, where that you can use formulas to calculate the median of an information set. We’ll stroll you via a way to try this within the illustration below, which features an inventory of customers along with their enterprise size and earnings cycle size. For context, the two tables (normal vs. median) seem to be at the general revenue cycle size for each enterprise size. Download our free Excel guide here for more tutorials to help you master the essential Excel skills.
    able to see how it works? down load the file right here to observe along with the directions under.
    The Excel Pivot desk alternative for Calculating Median
    Excel_screen_grab-1.png
    The “average of income Cycle (Days)” table became created with a pivot table. The “Median of revenue Cycle (Days)” table became created by doing right here:
    1) Create a column with the six viable “employees” alternate options: 1 to 5, 6 to 10, 11 to 15, etc.
    EXstep1.png
    2) in the mobilephone to the appropriate of the “1 to 5” cost, classification right here:  =MEDIAN(IF($ A$ 2:$ A$ 20=D2, $ B$ 2:$ B$ 20)) ex2.png three) if you happen to’ve closed the remaining parenthesis and whilst you’re still within the cell, class control+Shift+Enter (on a laptop) or Command+Shift+Enter (on a Mac) to populate the median. here is how you inform Excel that you simply wish to create an array.
    be aware: once you try this, you're going to see curly brackets appear around your components. in case you type in the curly brackets yourself or replica/paste the formulation above into the cellphone, Excel gained’t understand what they imply.
    ex3.png
    four) replica the median volume within the first mobilephone (G2 in the illustration) into the rest of the empty cells in the table (G3-G7 within the instance).
    ex4.png
    here's a breakdown of what these method inputs mean: 





  • here's the column of values for “# of employees” in Column A
  • here is the contents of telephone D2: “1 to five”
  • this is the column of values for “sales Cycle (Days)” in Column B

  • Translation: First, the IF commentary finds all rows the place the # of employees = “1 to five”; it then retailers all the corresponding “sales cycle” values in an array. The MEDIAN characteristic then pulls the median out of that array of income cycles for the “1 to five” customers.
    When to use Median to research information
    regular (or mean) and median are average measures of records, but generally talking usual is extra commonly used than median.
    whereas general is a positive technique to examine what a typical member of a knowledge set appears like, in some instances, median truly gives a fuller photo of a knowledge set within its context.
    really, common can in fact be misleading if the information set is particularly skewed and a big set of the population is equivalent with several a ways-away outliers. In such circumstances, the median is a much better indicator of what a standard member of the facts set looks like.
    for example, in the u.s., household salary is generally measured and mentioned in terms of the median. This may be as a result of the fact that high-earnings inequality nationwide would make the average a poor illustration of a typical American family’s revenue. 
    here’s another scenario: If a business have been inspecting earnings, it would be crucial for them to accept as true with the kinds of records in the units they were examining. for instance, calculating the standard volume of total monthly sales can be a very good indicator of efficiency because every month has roughly the identical variety of days and alternatives to promote, so the commonplace would exhibit what a baseline expectation of productivity could be.
    besides the fact that children, the ordinary may no longer be the highest quality measure to analyze every sale’s size that year. If the organization bought items various in price from $ 10 to $ 25,000, then the commonplace sale dimension should be would becould very well be skewed with the aid of the bottom and highest prices. instead, the median would give the organization a concept of if revenue had been customarily better or lessen in fee. See the change?
    subsequently, inspecting each the commonplace and the median will supply probably the most full photograph possible of your organization’s raw records, and pivot tables in Excel, such because the ones above, can assist you analyze both records units.