BNMC Blog

Tip of the Week: Handy Excel Functions You May Not Have Known About

Tip of the Week: Handy Excel Functions You May Not Have Known About

There are a lot of functions and capabilities buried in Microsoft Excel, many of which are unknown by most users. This is a real shame, as the value of Excel as a business tool is considerably larger than the simple organization that many see as its limits. To help counteract this, let’s go over a few useful utilities that Excel enables, if you know how to access them.

Count Specific Cells

If you need to take a tally of the number of times a value appears in your spreadsheet, doing so is simple. Using Excel’s COUNTIF function enables you to automatically total up all cells in a given range that meet a given criteria.

Once all your data is entered into a column of your spreadsheet, select a new cell and label it with the data you want to track. In the cell below it, enter the formula =COUNTIF(, then select the range you want to count within (in our case, whichever column contains your data). Once that’s added to your formula, add a comma, and then specify which value you want to tally up in quotes (or, if you’ve labelled another cell with the value, simply select the appropriate cell. Close the parenthesis on your formula, hit enter, and you’re all set.

In our example, we get a count of four for “Maggie” by using the formula =COUNTIF(A:A,D1). Alternatively, =COUNTIF(A:A,”Maggie”) would work, too.

Switch Value Format

Here’s the thing: there are a lot of formats that are a pain to type out individually, especially if your raw data isn’t converted. Fortunately, Excel makes it a lot simpler than typing each value out in the correct format. Rather than going through the motions to adjust to the proper formatting for each one, it can be done en masse with just a few clicks. For instance, to change your values to currency, all you must do is highlight the cells you need to change and press Ctrl+Shift+$.

This allows you to turn this:

…into this:

You also have the option to change it using the toolbar. Under Home, you should see a section labelled Number. From there, you have a few quick options to adjust the formatting, including into a few different currencies, as well as a drop-down box with plenty of other options available.

Nicer Formatting

Let’s go back to some basics for a moment with some basic formatting best practices. Without proper formatting, a spreadsheet can be a pain to glean any decent information from, but with the right rules in place, it can quickly gain exponentially more use. Let’s go through a few simple basics to help make your spreadsheets more comprehensible.

Let’s say, for the sake of our example, you wanted to take stock of some of the items in your office. Simple enough—you’d probably begin your list with the title (“Supplies”) and then list what it was you were trying to organize.

However, with your items varying in length, the spreadsheet could quickly become confusing. Fortunately, this can be fixed by selecting the column and pressing Alt+H+O+I.

Don’t worry too much if you realize you missed an item… you can always add another row by pressing Ctrl+Shift+Plus Sign.

Now that you’ve compiled your list, you don’t want the title “Supplies” to interfere with your amounts. To avoid that, you can merge two cells (in this case, A1 and B1) into a single cell, where “Supplies” will be written out. Select them both and press Alt+H+M+M. Feel free to align your text to the center, as well, by pressing Alt+H+A+C, or by using the icon in the menu bar.

Fill in the number of items you need in the next column over, and the price for each in the column after that (don’t forget to use tip two to change your prices to currency format).

Then, all you need to do is tell Excel to multiply the values in your number of items needed column with the values in your price column, and you’ll have the total needed for each item.

For extra credit, you can then easily add up these costs. Select a new cell, and then click on AutoSum in the Home menu bar. Then, select your final costs, press Enter, and you’ll have the total investment that your supplies will require.

Hopefully, these Excel shortcuts will come in handy. What are some of your favorites? Leave some shortcuts for us to try in the comments!

 

Comments

No comments made yet. Be the first to submit a comment
Already Registered? Login Here
Guest
Tuesday, September 22 2020

Captcha Image

By accepting you will be accessing a service provided by a third-party external to https://www.bnmc.net/

Subscribe to Our Blog!

Mobile? Grab this Article!

Qr Code

Tag Cloud

Tip of the Week Security Technology Best Practices Productivity Business Computing Privacy User Tips Email Cloud Network Security Microsoft Efficiency Business Hackers Hosted Solutions Software Computer Data Malware Communication Workplace Tips Google Small Business IT Services Hardware IT Support Mobile Devices Cloud Computing Tech Term Smartphone Internet Data Recovery Managed IT Services Android Users Backup Innovation Ransomware Mobile Device Windows VoIP Outsourced IT Phishing Information Smartphones Saving Money Upgrade communications Data Backup Facebook Windows 10 Miscellaneous Disaster Recovery Office 365 Network Server Browser Microsoft Office Cybersecurity Social Media Business Continuity Business Management Productivity Managed IT Services Passwords Internet of Things Quick Tips Data Management Windows 10 COVID-19 Vulnerability Gmail Word Remote Monitoring Gadgets App Apps Artificial Intelligence Chrome Managed Service Provider Mobile Office Money Conferencing Save Money Managed Service Infrastructure Encryption Analytics Virtual Reality Collaboration Networking Remote Work Government Settings Access Control VPN IT Support Employer-Employee Relationship BYOD Applications Router Paperless Office Information Technology Website Google Drive Company Culture Tip of the week Remote Workers Big Data Storage Firewall Development Maintenance Document Management HIPAA Hacker Data storage Computers BDR IT Management Data Security Business Technology Spam Virtual Private Network Display WiFi YouTube Operating System Risk Management Robot Bandwidth Employee-Employer Relationship Office Tips Content Filtering Unified Threat Management Automation Avoiding Downtime Education Antivirus Apple Data Breach Windows 7 Remote Computing Printing Break Fix Downtime Password Search Software as a Service SaaS Marketing Wi-Fi Virtualization Wireless Holiday Solid State Drive Mobile Security Server Management Training Touchscreen Retail Alert Computing Outlook Managed IT Bring Your Own Device Hard Disk Drive iOS Social Tablet Compliance Business Intelligence LiFi How To Hosted Solution Humor Monitors Administration Vendor Management Best Practice Chromebook Regulations Mouse Managed Services Data loss Two-factor Authentication Augmented Reality Going Green Telephone Systems Managed IT Service MSP Laptop IT End of Support OneNote Managing Stress The Internet of Things desktop Customer Relationship Management Scam Social Network Bitcoin Digital Heating/Cooling Multi-Factor Security Chatbots Safety eWaste Google Wallet Meetings Disaster Mobility Batteries Star Wars Printer Holidays Wearable Technology Network upgrade Co-Managed IT Banking Nanotechnology Business Growth Time Management Alerts Network Congestion USB Black Friday Onboarding Procedure GDPR Motherboard Cables Cybercrime Legal Distribution Managed Services Provider Fraud Cost Management Windows 8 Spyware Servers Google Calendar Slack Hard Disk Drives Identity Continuity Unified Communications Drones Uninterrupted Power Supply Emergency Print Toner Myths Computer Care IBM Network Management Payment Card Messaging Writing Smart Office File Sharing Dark Web Content Huawei Screen Reader Charging Licensing Dongle Mobile Device Management Cyber Monday Digital Signage PCI DSS Data Warehouse Wires Deep Learning Logistics Entertainment Vendor Dark Data Assessment Update Identity Theft Legislation Monitoring Solid State Drives Solar Shortcuts Mobile Management Corporate Profile SharePoint Inventory Employee-Employer Relationships Work Microsoft Excel Streaming Processor Smart Tech Halloween Alt Codes Cooperation Service Level Agreement Automobile Smart Technology Troubleshooting Battery Business Analysis Voice over Internet Protocol Politics G Suite Techology Asset Management Hybrid Cloud Hotspot Customer Service Internet Exlporer Statistics Spying Peripheral National Security Work/Life Balance Budget Dell Lenovo Security Cameras Profiles WannaCry Downloads Analysis Shadow IT Students IT Technicians Office Optimization Samsung Cabling Mail Merge Blockchain Human Error Memory Connectivity Health Private Cloud Scary Stories Typing Staff Leominster Current Events Google Maps Shortcut Distributed Denial of Service Files Virus Modem FinTech Tech Support Running Cable VoIP Unified Threat Management Memes Scalability Language Value Buisness Bluetooth Comparison Management Updates Firefox Superfish Utility Computing Gamification Mixed Reality Mirgation Social Engineering Recycling Virtual Desktop Address Permissions Device Reducing Cost IoT Sports Cryptocurrency Migration Consulting K-12 Schools Twitter IT solutions Patch Management LED RMM Threats Cookies Websites Google Docs User Error Computing Infrastructure Professional Services Hacks Digital Payment Specifications PowerPoint Policy Social Networking Laptops Tracking Crowdsourcing Financial Personal Information Botnet Cleaning Cortana 3D Printing CCTV Webcam Processors Machine Learning Error Unsupported Software Point of Sale Ben McDonald shares Travel Computer Repair Regulation Relocation Mobile Computing Mobile Data Recovery Supercomputer CrashOverride How To Staffing Motion Sickness Black Market Administrator Printers Taxes IT Budget Law Enforcement Web Server what was your? GPS Notifications Electronic Medical Records Physical Security IT Consultant Emoji Upgrades Gadget Cameras

What Our Clients Say

  • BNMC has provided us with nothing less than outstanding service and results for all of our IT needs for the past few years. Every member of their staff is professional, knowledgeable, friendly and eager to solve any problem...
  • 1
  • 2
  • 3