ALL POSTS BY: Doug Midkiff
Excel Tip of the Week: The Finale
Today’s tip is the 50th and final Excel Tip I will be writing for OwenBloggers. I’ve really enjoyed writing these tips over the past year and I hope you’ve enjoyed reading them. Hopefully some of you have learned a few things.
People have asked me before how I became good at Excel, so for this final post I wanted to give some practical advice on how to improve your Excel skills.
Use Excel – This sounds so obvious, but you can’t get good at Excel unless you are using it frequently. This is why almost every single Excel tip I wrote has an example spreadsheet for you, the reader, to get hands-on experience. But you should go beyond that. Use Excel for random stuff like making a grocery list or tracking your workouts. You might end up discovering something in Excel you didn’t know was there or get inspired to learn something new just to see if it will work.
Excel Tip of the Week: Calculation Options
Today’s topic is calculation options.
In Excel you can specify a few different ways as to HOW the program calculates your numbers. This might sound a bit odd to a few of you, but hopefully the explanations of the different options will clear things up.
There are a couple ways to view the calculation options. The first way is on the Ribbon under the Formula menu. If you click into the formula menu and then look to the far right you will see the calculation options. (Keyboard shortcut Alt → M → X) However, the options shown in the Ribbon are a small sampling of the full set of calculation options available (and on a Mac it is even more limited).
The other way to look at the calculation options is to open the Excel Options (File → Options) and then clicking on “Formulas.” (Mac: From the menu bar click on Excel then Preferences then select the Calculation icon.)
Excel gives three different calculation options that are mostly self-explanatory:
1) Automatic: This is the default mode. In this mode Excel will recalculate every formula in the spreadsheet each time something changes.
Owen. Wired.
When you get tired of going to the Starbucks across the street, OwenBloggers has you covered.
JJ’s Market and Café
Website: N/A. Here is their page
Address:
Hours: They just scaled their hours back a bit: Mon-Fri – 7a-11p; Sat – 8a-11a; Sun – 10a-9p
Seating: Lots of seating with a ton of tables as well as comfy chairs/couches
Outdoor Seating: Yes, kind of. Nothing to get excited about.
Selected Prices: Small/Large Coffee: $1.60/$1.85, Medium Latte: $3.35;
Outlet availability: Decent. They have several power strips throughout, but there are still a few spots in the place where you can’t reach an outlet.
Wifi: Yes. Closed network, password required.
Driving Distance from Owen: 0.2 miles (Winner closest indie coffee shop to Owen!)
Parking (Car): Yes, but it is a small lot. Best option is to walk from Owen.
Parking (Bike): No.
Food: They have a few cookies and muffins; also JJ’s is one part coffee shop and one part mini-grocery store. They sell things like granola bars, chips, beer, kegs (it’s actually where we get our kegs for Thursday Social), candy, paper towels, etc. Also of note is that they have international stuff from Italy, Belgium, Sweden, etc.
Scene: Majority Vanderbilt students. Almost everyone at JJ’s has their laptop open with papers spread out everywhere. Law/Divinity/Owen and undergrads all flock to this place.
Other Notes: JJ’s has 5 craft beers on tap, a great selection of loose leaf teas, and plays classical music quite a bit.
Excel Tip of the Week: SUMIFS & COUNTIFS
Today’s topic is SUMIFS and COUNTIFS.
You might be thinking, “Wait a minute Doug, you already covered this topic!” But look again at those formula names, there is an “S” at the end of them indicating a completely different and more advanced function.
These two formulas are actually new to Excel as of 2007. These formulas have helped reduce the need for moving data into Microsoft Access or coming up with super complex array formulas. As their names might suggest these functions allow the user to sum or count data based on multiple criteria instead of just a single criteria.
Here are the inputs for the SUMIFS function:
Notice how “sum_range” is the first input in this for formula which is the opposite of the SUMIF function. Also notice the ellipsis at the end which indicates that more inputs can be received. This formula can actually handle up to 127 different range/criteria pairs.
Here are the inputs for COUNTIFS:
COUNTIFS is very similar to SUMIFS just without the “sum_range.” This formula can also hold up to 127 different range/criteria pairs.
Let’s look at an example. Download the example spreadsheet here.
Excel Tip of the Week: Protecting Worksheets
Today’s topic is password protecting your worksheet.
In my professional career I’ve had to make many spreadsheets for other people to use. This is always a tricky situation because regardless of Excel skills, there will always be an information asymmetry between the developer and user of the spreadsheet. Even if the spreadsheet developer uses the most clear formatting and annotation there is still a risk for the user to delete something, insert something, or paste something where it isn’t supposed to go.
To help combat this, it is sometimes wise to password protect a spreadsheet. Excel actually gives you quite a bit of flexibility when it comes to password protection. You can protect just a single cell of a spreadsheet all the way to the entire sheet and you also have a wide array of protection options. For example, you can set the parameters such that users can change the cell color, but can’t change the cell value. Or you could allow users to sort the data, but not use the Auto-Filter. Let’s look at some basic examples:
Download the example spreadsheet here.