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:

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:

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:

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:
  • 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:

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