Contextures

Contextures News

Manual Calculation Warning

Mar 22, 2016

Quickly see if calculation is set to Manual, INDEX/MATCH functions, and more, in this week's Excel news. If there are topics that you'd like to see covered in future newsletters, please let me know.

 -  Debra -  ddalgleish @ contextures.com
     Visit my Excel website for many more tutorials and videos.

Manual Calculation Alert

In a large workbook, with lots of formulas, it can be quicker to make changes if you temporarily turn the Calculation setting to Manual. In most cases though, you want to change the setting back to Automatic calculation, before you close the workbook. Otherwise, the formula results won't automatically update, and you could be using outdated information. Also, if you leave the Manual setting on, other workbooks that you open in the same session will use that setting.

Make sure that you know when Manual calculation is on:

  • To quickly check the Calculation status, you can add the Manual and Automatic commands to the Ribbon or the Quick Access Toolbar (they're listed in the "All Commands" drop down). That's what I use, and you can see my example here.
  • Or, use the INFO function -- it shows information about the current operating environment. The INFO function takes one argument -- the type_text. One of the text_type options is RECALC, and that shows the current recalculation mode -- Automatic or Manual.
  • NOTE: If you type that formula onto the worksheet, and press Enter, it will show the setting. If the formula was previously entered, press the F9 key to force a recalculation, to be sure that the result has been updated.

Your Feedback

After last week's article on workbook navigation, Peter G. sent me this great tip: "I find the quickest way between sheets is with Ctrl + Page up or Ctrl + Page down."

Do you use those shortcuts? If you have any tips or questions after reading this week's newsletter, please let me know.

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

INDEX / MATCH -- JD Sartain shows how to combine INDEX and MATCH with other functions . They're flexible and powerful functions, and it's worth taking time to learn about them. (Level - Intermediate) NOTE: Site has ads - turn off sound.

Dashboards -- Before you start working on an Excel dashboards, see what Chandoo and the Excel TV team have to say about the requirements gathering process for dashboards. Spend time at the beginning, to save time later. (Level - Intermediate)

Data Insights -- Remember to register to watch free online sessions, live-streaming from Microsoft's Data Insights Summit, today and tomorrow (March 22-23). There will be some great sessions, so try to catch them. (Level - Intermediate/Advanced)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog.

The New Bike

Our granddaughter came to visit last week, for March Break. And, since tomorrow is her birthday, she got an early present -- a new bike. She had her eye on a Disney Princess helmet (pink, with a sparkly crown), but we opted for this sturdy-looking model instead. Being fashionable is fine, but not at the expense of safety!

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:

www.contextures.com/newsletter/excelnews2016/20160322ctx.html

Recommended Excel Products

  • Pivot Table Tools: Save time and frustration when working with Excel Pivot Tables. My Pivot Power Premium add-in saves your preferred settings, so you can apply them all with a single click. Also, quickly change all or selected fields to Sum, change the number formatting, and remove "Sum of" from all the headings. And many more time-saving feaatures!

________________________

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.

 

 

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: June 14, 2017 11:47 PM