facebook linkedin twitter

Computerware Blog

Other Options for Visual Excel Data

Other Options for Visual Excel Data

If you need to condense data into a concise and comprehensible form, Microsoft Excel is tough to beat. This is despite the fact that your average spreadsheet isn’t exactly an eye-catcher. However, Excel provides a few native options that boost your data’s visual appeal, making it more engaging and much more meaningful. We’ll review a few today.

In-Cell Charts and Sparklines
One of the biggest downsides to line after line and row after row of numbers and labels is that it becomes difficult to visualize any patterns. Both of these functions resolve that by providing clear visual cues.

Excel’s Sparkline option displays a miniaturized line graph, bar graph, or win/loss chart within a cell. This adds an added layer to the visualization. So, let’s say you want to track how many sales were made of a certain product or service offering. With a line graph Sparkline, you can create this quick reference:

Excel’s Sparkline option displays a miniaturized line graph

You also can go even more in-depth with your styling, applying custom rules to your cells to display more granular information. Let’s assume that your goal for these specific sales was to complete at least 16 per month. Utilizing conditional formatting can make it even more obvious when this goal was (and wasn’t) reached.

With your data selected, select Conditional Formatting from the menu ribbon. Click Manage Rules and access your newly-created rule for the data set. You can then play with some of your options. In this example, we decided to use arrows as our icon style and identify 16 as both of our top values and 15 for the bottom. After you hit Apply, your data should appear like this:

using arrows as our icon style

As a result, you can see much more quickly which months saw enough sales to reach the set goal, and which did not. This makes it easier to identify other events that may have influenced these numbers and act accordingly.

Turning Pies into Sunburst Charts
A sunburst chart is effectively the combination of multiple pie charts into a single, unified representation. This helps you to spot important patterns that may have been missed if the graphs were left separated.

For example, let’s say you serve a variety of industries, and have specific services for each, with each service divided into tiers. By labelling each industry as 1,2, and 3, with services A, B, and C, and the tiers X, Y, and Z, you can create a sunburst chart that allows you to compare how these sales play as a part of your incoming revenue.

One this data has been divided as pictured, you can apply the sunburst chart to see the following hypothetical:

Turning Pies into Sunburst Charts graphic

So, while you can see from the data that service 3CY outperforms any other on this chart (most by a considerable margin) by reviewing the data, the sunburst chart provides considerably more insight into this fact - primarily, the significance of such a difference. From there, you can again examine some of the factors surrounding these outcomes and adjust accordingly.

Raising the Bar with a Thermometer Chart
Thermometer chart are great for providing some motivation to your team toward a goal, although it does tend to be used primarily for financial benchmarks. However, this one can be a challenge, so be careful as you follow along.

Sticking to the sales usage, let’s say our penultimate goal is $150,000, and there has been $37,163.48.

  • In Excel, insert cells that look like this:
    insert cells graphic
  • Selecting them, click Insert, and under the Column Chart options, add a Clustered Column Chart item. With this item selected, click Switch Row/Column in the menu bar at the top of your screen.
  • Right-click the Total Sales bar and select Format Data Series, then in the Series Options page, select Secondary Axis. You should now only see one bar, with labels on both sides.
  • Right-click the label on the left and select Format Axis. The Axis Options Pane should appear. In the box for Maximum, type in your goal of $150,000. Repeat this process for the label on the right.
  • Right-click the Goal Bar and select the Format Data Point option. Under Fill & Line in the menu that appears, select No fill and make the border a Solid line. Repeat this process with the Total Sales Bar, skipping the “No fill” part and selecting the color you want instead.
  • Finally, you can delete the labels on the bottom and sides (if you wish), as well as the horizontal lines, leaving you with the finished product:

bar graph graphic

What kind of data would you express better, now that you have these tools to leverage? Share your thoughts in the comments!

Why Protecting Your Google Account Should Be a Pri...
Get the Technology Your Company Requires


No comments made yet. Be the first to submit a comment
Already Registered? Login Here
Thursday, June 20, 2019

Captcha Image

Mobile? Grab this Article!

QR-Code dieser Seite

Tag Cloud

Security Tip of the Week Technology Best Practices Privacy Internet Cloud Business Computing Business Management Software Hackers Data Microsoft Backup Managed Service Provider Workplace Tips Hardware Productivity Network Security Email Business Hosted Solutions Malware Saving Money IT Services Efficiency IT Support Google User Tips Small Business Computer Smartphones Innovation Mobile Devices Quick Tips Productivity Business Continuity Gadgets VoIP Social Media Network Android Microsoft Office Disaster Recovery Collaboration Server Data Backup Communication Mobile Office Data Recovery Virtualization communications IT Support Smartphone Upgrade Mobile Device Management Miscellaneous Phishing Tech Term Cybersecurity Ransomware Windows 10 Windows Facebook Unified Threat Management Passwords Apps Holiday Remote Monitoring Operating System Vendor Management Automation Analytics Users Managed IT services Mobile Computing BYOD Remote Computing Internet of Things Mobile Device WiFi BDR Outsourced IT Marketing Data Management The Internet of Things Apple Firewall Artificial Intelligence Managed IT Services History Alert Browser Save Money App Cloud Computing Encryption Big data Spam IT Consultant Going Green Two-factor Authentication Gmail Bandwidth Business Technology Office Help Desk Bring Your Own Device Maintenance Antivirus Printer Windows 10 Health Content Filtering Office 365 Lithium-ion Battery Training Hard Drives Best Practice Information Technology Budget Outlook Saving Time Windows 8 Computers Government Managed IT Wireless Technology Employer-Employee Relationship Cybercrime Virus Tech Support Search Cost Management Hiring/Firing Healthcare Access Control IBM Customer Service VPN It Management Travel Blockchain Education Recovery Update Networking Hacking Information Money Phone System Document Management Proactive IT SaaS Running Cable File Sharing Law Enforcement Applications Augmented Reality Hard Drive Intranet PowerPoint Twitter Compliance Managed Service Mobility Website Data storage Humor Administration Risk Management Password Avoiding Downtime Legal Save Time Downtime iPhone Regulations Sports Company Culture Disaster Excel User Computing Computer Repair End of Support Social Networking Hosted Solution Social Robot Customer Relationship Management Data Security Securty Wireless Fax Server Google Maps DDoS Patch Management Solid State Drive Data Loss Social Engineering Flexibility Scam Black Market Word Retail Cryptocurrency Data Breach Laptop Value Business Intelligence Private Cloud Cortana Business Growth Digital Digital Payment Project Management Telephony Hacker Websites Router Management Machine Learning Vulnerabilities Telephone Systems Comparison Tablet Current Events Paperless Office User Error Trending Presentation Cooperation Co-managed IT Software as a Service Unified Communications Settings Mouse Public Cloud Vendor Uninterrupted Power Supply Virtual Desktop Specifications Safety Vulnerability Statistics Bitcoin Programming Identity Theft Data Protection Windows 7 IT service Network Congestion Documents Lifestyle Teamwork Heating/Cooling Gaming Console Distribution Entertainment Downloads Politics Storage Virtual Reality Webcam Emails Bluetooth Streaming Media Text Messaging Conferencing Automobile Chromebook Computing Infrastructure Hack Voice over Internet Protocol Redundancy Google Docs Net Neutrality Monitoring How To eWaste Chrome Download Evernote Experience Monitors Virtual Assistant Cleaning Inbound Marketing Office Tips Computer Accessories Microchip Taxes Processors Start Menu Tech Terms Distributed Denial of Service USB Mobile Technology YouTube Pain Points Bookmark Computer Care Micrsooft LinkedIn Device security MSP Employer Employee Relationship IP Address Microsoft Excel G Suite Drones Desktop Finance Hacks Physical Security Application Google Play Webinar Google Drive Windows Server 2008 Nanotechnology Social Network Licensing Human Resources Employer/Employee Relationships Business Cards Service Level Agreement Banking Botnet Processor Spyware Mobile Payment Upselling Skype Startup Asset Tracking Relocation Tablets Microsoft 365 Archive Browsers Electricity Printing Username Medical IT Permissions Language Video Surveillance Unified Threat Management Backup and Disaster Recovery Sync Saving ZTime Gamification Notifications CCTV Digital Signature Security Cameras Point of Contact Screen Reader Customer Resource management User Tip Proactive Maintenance Piracy Upload Business Metrics Computer Malfunction Navigation cache Television switches Writing Smart Tech Reliable Computing Solutions Music Data Analysis Best Practives WannaCry Upgrades Harddrive HTML Professional Services Debate Fiber-Optic Database In Internet of Things Corporate Profile Recycling Mobile Security GDPR Time Management Freedom of Information LiFi GPS Analytic Telephone System PDF External Harddrive Software License Trends Motion Sickness Best Available Keyboard Tech Assessment SharePoint Product Reviews Multi-Factor Security Cost Scalability Integration Microserver Fileless Malware Google Calendar Deep Learning Devices Messenger Tracking Domains Tip of the week Work/Life Balance Read Fraud Tactics Proxy Server Get More Done VoIP Thin CLient UTM Content Rental Service Meetings Public Speaking Modem Touchscreen News Visible Light Communication Memory 3D Ebay Device Management Smart Phones IT Plan Enterprise Resource Planning Web Server Shortcut Term Printer Server Consultation IT consulting Legislation Error Cabling Electronic Medical Records Transportation Turn Key Knowledge Wi-Fi Administrator Entrepreneur Directions Trojan Technology Tips Business Managemenet Accountants A.I. Books Workers Troubleshooting Recording Content Management email scam Addiction Dark Web Audit CIO Leadership Multi-factor Authentication Supercomputer HIPAA SSID Organize Fake News Advertising Flash Adminstration Pirating Information Environment Staffing Google Wallet online Currency Migration Connected Devices Competition Processing Data Warehousing Windows XP Wireless Headphones Telephone Worker Electronic Payment Health IT Employee/Employer Relationship Network Management Managed IT Service Access Displays Equifax Fleet Tracking Reading Samsung Business Owner Logistics Emergency Hard Drive Disposal Society Productuvuty Managing Stress Printers SQL Server CrashOverride Regulation Azure 3D Printing Capital