If you refer to the same data in an Excel sheet every day, sure, you may know just where to find the information you need. In a large workbook, however, you may lose track of exactly where you placed some crucial data. Start naming cell ranges to easily refer to information you need.
Stop remembering random letters and numbers: Provide context to spreadsheets by using Range Names
To begin naming cell ranges, follow the steps below:
1. Select the range of cells you would like to name. Remember: a cell range is a block of one or more cells. In this example, I want to be able to quickly refer to my 2017 Feed Expenditure information no matter where I am within the Excel workbook.
2. Next, click on the Formulas tab in the Excel ribbon.
3. With the desired range still selected, click the Define Name in the Defined Names portion of the Formulas tab.
4. When giving a range a name, you cannot use spaces or any special characters except an underscore. Also, be sure to double check your cell ranges. Press OK to continue.
5. A shortcut method for performing this action is to select the desired cell range, place your cursor in the Name Box, and simply type a name.
6. Now, you can reference theses ranges in formulas and functions across the workbook rather than typing a traditional cell range.
7. Lastly, if you need to rename any of your ranges, just click the Formula tab again and select Name Manager.
Trying to figure out just what Excel is telling you when an error occurs in your spreadsheet? Here are some of the most common Excel errors and how to fix them.
Remember: Excel will display a small green triangle in the upper left corner of the cells containing error values. Clicking on that green triangle will prompt a yellow diamond to appear with options for correcting the error.
#VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing." There are a number of reasons this might occur, but it’s always good to check if you included spaces, characters, or text in a formula where Excel was expecting a number.
When you see this error, don’t worry. All it means is that the column isn’t wide enough to display the information you’ve included.
Just like in regular ol’ math, you don’t divide by zero. When this message appears, make sure you’re not accidentally dividing by zero or—which is more likely the case—referencing a blank cell.
The #REF! error shows when a formula refers to a cell that’s not valid. This happens most often when cells that were referenced by formulas get deleted, or pasted over.
Getting a #NULL! error all depends on your spacing in a formula. For example, maybe you forgot to include a mathematical operator such as the plus sign ( + ) and included a space instead, like =B2 B4+B6; Or maybe you forgot the colon ( : ) for selecting a range and accidentally wrote =SUM(A1 A10). Double check your formulas and functions for extra spaces!
Excel shows this error when a formula or function contains numeric values that aren’t valid. This often happens when you’ve entered a numeric value using a data type or a number format that’s not supported in the argument section of the formula. For example, you can’t enter a value like $1,000 in currency format, because dollar signs are used as absolute reference indicators and commas as argument separators in formulas. To avoid the #NUM! error, enter values as unformatted numbers, like 1000, instead.
Photo courtesy of Where Marketers Go to Grow.
Looking to get something for the techie in your life without breaking your budget? Here are some of this season’s notable items:
Amazon Echo Dot - $39.99
Amazon’s way of entering your household with Alexa. Tap into cloud services, activate connected home devices, play music, or ask questions like “What’s the score of the Cardinals game?” Alexa’s there to help.
Photo Courtesy of BGR.com
Nintendo NES Classic - $59.99
This tiny, retro console comes preloaded 30 great games from
the NES era. Relive games like Super Mario Brothers, Megaman 2, and Punch Out.
The only problem? With so much hype, finding this hot-ticket item on any shelf
will be a challenge.
Photo Courtesy of Nintendo.
Tile Mate - $24.98
Always misplacing your stuff? Less than 5 millimeters wide, this tracking device syncs with your phone to map the last location of your lost valuable, chirps and sings when you’re looking for said item, and gives you directions to it.
Forgot where you put your phone? You can also use Tile to locate your synced mobile device.
Photo Courtesy of SlashGear.com.
Google Chromecast - $25.00
Yes, Smart TVs are here. But the functionality of a set top box device is way better. Whether you’re looking to cut the cable cord or you just want a way to stream your favorite entertainment, Chromecast turns your mobile device into a powerful television remote for some of the best TV shows you can find.
Photo courtesy of Google.
Samsung Gear VR (2015) - $59.99
Although not the latest virtual reality headset, you can experience new worlds for almost half the price of the VR 2016. Lightweight and cordless, this headset has ushered in the world of VR gaming. The only downside? It only works with some recent Samsung phones
Photo Courtesy of Samsung.
D-Link Day & Night Wi-Fi Camera - $39.99
Need to do some surveillance around your home? Great for parenting or recording wildlife from your house, this small wireless camera transmits video (and audio) to its D-Link App. What does that mean for you? You can view the video stream from your computer, phone, or any device with web interfacing. With quick set up and features like infrared vision and motion alerts, this cheap gadget provides security on a budget.
Photo Courtesy of D-Link.
Although it has become increasingly easier to find WiFi or stay connected to a mobile network, there may be a time when you need to work on a Google Doc, Sheet, Slide, or Drawing and don’t have access to the internet. Let’s say you have a long flight during the holiday season. You board the plane and realize there's no WiFi. There goes an opportunity to get some work done. To avoid this, follow the directions below and get ready to go off the grid!
Just remember to keep sensitive files off of Google Drive and on a Columbia College backed location, such as your personal I:Drive, department drive, etc.
Here’s what you need to do before your trip:
For a Windows Laptop:
1.Open Google Chrome.
2.Make sure you are signed into your Google Account.
3.Go to drive.google.com/drive/settings.
4.Look for the “Offline Sync” and click “Turn On”.
For an iPad:
1.Open Google Docs.
2.If you are using an iPad to work on Docs offline, click the Settings icon at the top left of the screen.
3.Press the Offline Icon.
Other Tips before Going off the Grid:
Before you leave for your trip, make sure you’re signed into Google on Chrome. Another tip to avoid any hiccups is having the documents opened on Chrome before you leave. If you plan on using a mobile device to access your work, check that you have all necessary apps downloaded.
Photo courtesy of PC Magazine.
Having trouble printing an Excel spreadsheet to one page? Is your printer cutting off valuable information? Luckily, there’s a quick solution. What it comes down to is setting your Page Size and Print Scale:
Setting Page Size
1. Select Page Layout in the Excel ribbon.
2. Select the Size button.
3. Next, choose your desired page size. For this scenario, I’m choosing a Standard Letter Size.
Setting Print Scale:
1. Staying on the Page Layout ribbon, click the Scale to Fit Launcher.
2. Choose your scaling option from this section of the Page Setup Dialogue window. To make the spreadsheet fit to one page, click Fit To and then press Okay.
Your Excel spreadsheet should now fit to one page. If you choose, you may also fit the spreadsheet to more pages by simply increasing the amount in the Fit To field.
Time is precious. If you’re not using the following Windows keyboard shortcuts, you should definitely start.
Tech Tip Tuesday: 5 Underutilized Keyboard Shortcuts
*Note: "WIN" refers to the key and “+” means to press the designated key simultaneously with the following key.
1. CNTRL+L - Find the Address Bar
Once you’re ready to go to a new website, simply press CNTRL+L to automatically return to the address bar rather than using your mouse.
2. WIN+D – Minimize & Open All Windows
Working with sensitive information? Instead of going through each window and selecting the minimize button, pressing WIN+D will quickly hide every program open on your desktop. Ready to view them again? Just press WIN+D a second time.
3. CNTRL+F – Go to Find Bar
With access to a number of different web-browsers, it can become easy to forget where the Find Bar is located. The Find Bar is used to look up specific text within a webpage. Just press CNTRL+F and begin searching.
4. WIN+L – Lock your Computer
Need to get up from your desk quickly while making sure your computer isn’t vulnerable? Press WIN+L and your account will be locked until you’re ready to log on.
If you’re like me, you keep a number of tabs open on your web-browser while surfing the Internet. By pressing CNTRL and any number 1 through 9, the browser will rapidly switch between tabs, making it easier to get the information you need as quickly as possible.
Photo courtesy of www.itjive.com.
Do you use Google Drive? Instead of accessing Google Drive from a web-browser, install Google Drive to your computer and save files directly to the cloud! This will save you the hassle of opening a website and transferring files every time you back up documents. Just remember to keep sensitive files on a Columbia College backed location, such as your personal I:Drive, department drive, etc. See below for installation instructions:
Installing Google Drive to Your Computer
Saving Files to Your Google Drive
That’s it! You can now save directly to Google Drive rather than pulling up the Google Drive or Docs on a web-browser.
Tech Tip Tuesday: Document Check Out in SharePoint
Document check out allows you to check out a document while you edit it, making sure no one else can edit. When you have the file checked out, you can edit it online or offline, and save it multiple times. When you finish editing and check the file back into the library, other people can see your changes and edit the file, if they have permission. And, if you decide not to make or keep any changes in the file, you can simply discard your checkout so you don’t affect version history.
For more about checking out documents, visit the Microsoft Help Center here.
About VersioningWhen versioning is enabled in site lists, you can track and manage information as it evolves. You can look at earlier versions and recover them, if necessary. That is very handy, for example, when people realize that earlier versions of an item might be more accurate than later ones. Some organizations retain multiple versions of items in their lists for legal reasons or audit purposes.
When you enable and configure versioning, you can retain versions each time an item it is edited. You need to decide how many versions you allow, and whether you want all site users to see all draft versions, or only be visible only to the originator and specific people in your organization. By default, versioning is turned off. To turn it on and implement your versioning decisions, you must either have Full Control or Design permissions.
Enable and Configure Versioning
1. In a list or library, in the list or library tab, select Settings