Mediastreet
Tag

Excel

Browsing
In News

How to Make A Dashboard in Excel: A Step-By-Step Guide

No Comments

By Danel Redelinghuys

A dashboard is an excellent way for businesses to consolidate their data and use it to improve various aspects of their operations. In this article, we will look at step-by-step instructions on how to make a dashboard in Excel. You can use Excel to create different dashboards, including a financial dashboard, sales tracking dashboard, product metrics dashboard, and more.

Continue reading for a deep dive on how to make a dashboard in Excel using interactive charts such as a pivot chart or a bullet chart. In this article, you will discover why Excel is essential and the benefits of using Excel to make a complex or simple dashboard. We also cover some advanced dashboard elements and features.

What Is a Dashboard in Excel?

A dashboard is a simplified visual representation of data relating to business performance. A dashboard keeps all essential data in one place so that managers can look at the data and make crucial decisions. A dashboard usually contains key performance indicators and compares data points.

There are three different dashboards that all have specific functions. A  strategic dashboard helps managers identify opportunities or forecast issues. An analytical dashboard helps managers find trends and make decisions quickly, while an operational dashboard helps managers monitor their employees and processes.

Why Learning How to Make a Dashboard in Excel is Useful

  • It will help you improve your Excel skills. Learning how to make a dashboard on Excel can enhance your skills and help you learn new aspects of this program. Many jobs use Excel for various tasks, so learning and practicing how to use it is extremely useful.
  • You can use Excel to make better visuals for your dashboard. By learning how to make a dashboard in Excel, you will also learn the best visuals for your dashboard depending on what data you need to display, whether in tabular format, a chart, or a graph. Your dashboard will report important values and data, so it must be easy to read.
  • You can explore new chart types and learn how to use them best. Sometimes, we get stuck in habits and avoid change, but you can embrace change in your dashboard creation and try various charts with Excel. Take the opportunity to experiment with a new chart type that you have never used before because formatting matters.
  • It will help you gain a new viewpoint on your data. It can help you spot problems and gain a new perspective on your data. When working on Excel to make a dashboard, you must enter the data into multiple places. It will help you choose the perfect visuals, such as a pivot chart or a doughnut chart.

How to Make a Dashboard in Excel: A Step-By-Step Guide

Step 1: Import the Data Into Excel

You cannot create a dashboard without a dataset. First, you’ll transfer the entire dataset you need to Excel. When making a dashboard from scratch using Excel, you don’t need to worry about scary formulas to move data. You can use an application programming interface, test file, copy and paste, or use Microsoft Power Query to help you transfer the initial dataset from your external data source.

Step 2: Create your Excel Workbook

Next, you must select the insert tab option to open three worksheets. One separate worksheet is for your raw data, the next one will be for the specific data going into your dashboard, and the third will be your dashboard tab. Creating three sheets will keep your work organized and ensure the easy creation of a dashboard structure.

Step 3: Make a Table and Insert Your Raw Data

The next step of this process is to insert your raw data into a table. You can insert the table on your first Excel sheet and name it “Raw data”. A table will help you read the data, making the next step easier.

People often refer to this stage as the data cleaning phase. Make sure you look for any missing data or mistakes during this stage. You will save real time and avoid major errors if you pay attention to the cleansing process.

Step 4: Analyze the Data in the Table

Once you have all the necessary data in one place, you must consider what you want to display on your dashboard. A sales dashboard may require different features than a project management dashboard. There are many dashboard examples online that can give you an idea of the cool features available.

Select the data you need for your dashboard, insert it into a table on the second sheet, and name it “Chart data”. This phase will help you determine additional tools you will use, such as a pivot table or excel formulas.

Step 5: Choose the Best Visual Representation for Your Dashboard

Now that you have all your data sorted, you should decide on the visuals for your dashboard and your dashboard background color. There are numerous chart types available in Excel for all dashboard types. You can use a pie chart, pivot chart, bar graph, column chart, gauge chart, bar chart, or dynamic chart.

For example, if you want to summarize your data in a table with statistics, you will use a pivot chart. If you need ideas, you can use a dashboard template or use an existing dashboard as inspiration. You will want to make sure to use an easy-to-understand format to ensure you get your vital metrics across.

Step 6: Construct your Excel Dashboard

Now you’re prepared to construct the actual dashboard. Open the third sheet and name it the “Dashboard tab”, select the “Insert” button and choose the type of chart you need, such as a pie chart, pivot chart, or a column chart. It will open a blank box.

Then, click “Select data,” open your chart data tab, select the data you need, and press “enter”. You will see your chart when you move back to the dashboard sheet.

Step 7: Customize the Dashboard

Now you can customize your stunning dashboard. You can modify chart colors, change fonts, add a chart title, and decide whether you want an interactive or static dashboard. Interactive dashboards allow you to change what data you see using a drop-down list or a slicer.

Use a dynamic chart if you want your dashboard to be interactive, and you can constantly update your dashboard with this type of chart. An interactive screen can grab viewers’ attention more than boring graphs, but may not be necessary to get your essential metrics across. You can add dialogue boxes and adjust each chart widget to make sure your dashboard is visually appealing.

Benefits of Making a Dashboard in Excel

  • It has excellent analytics abilities. Excel has statistical calculations built into its software and can handle complex calculations. The formula tab enables you to enter specific calculations that you need. This nice feature cuts down the average time it takes to analyze large data sets.
  • Excel is very flexible. Excel is fantastic for dashboards because it is flexible, and you can modify data or add additional chart options, such as a pivot table, at any time. Flexibility allows for correcting mistakes that you may have overlooked, and you can create a dynamic dashboard with the drop-down menu.
  • It is a software program that is easy to learn and use. Excel is an excellent option for creating various types of dashboards because it’s simple, and anyone can learn to use it. Many businesses and schools use excel, so most people have already come across and used this program.
  • Excel is a cost-effective tool. Excel has fantastic capabilities, as discussed above, and it has a much lower price than other software programs used to make dashboards. The average price of Tableau, a program that helps people understand data is $70 per month, while Excel only costs around $7 a month.
  • Utilize Microsoft’s Business Intelligence Visualization Software with Excel. When you use Excel to create a dashboard, you can also use Microsoft’s BI program , which helps you manage and store your data. Power BI and Excel work well together and improve your dashboard design experience.

Importance of Learning How to Use Excel Sheets

Learning how to use excel sheets is useful because it can open up new job opportunities and help you organize data better. Companies use Excel to share core performance metrics, create insightful reports, and even for customer service. Excel is part of Microsoft Office 356, a group of software programs that make our lives easier. Statista has mentioned that over one million companies worldwide use office 365 .

If you are interested in learning about using Excel sheets, you can consider enrolling in one of the best Excel bootcamps . Bootcamps are a short-term learning path and can quickly teach you everything you need to know. Other learning pathways available are online Excel courses, classes, and training .

How to Make a Dashboard in Excel FAQ

How can I make an effective dashboard?

You can make an effective dashboard by understanding the audience, using clear labels, and enhancing your dashboard constantly. You can use Excel to make editable reports that share key information on a single screen. This can be anything from feedback about customer satisfaction to an interactive to-do list.

What does a slicer on an Excel dashboard do?

A slicer is similar to a drop-down list, but a slicer is a filter you can see directly on a complex dashboard. You don’t need to click on an arrow or toggle button to show it. You can click on a word within the slicer to custom view the displayed data.

What industries use dashboards?

Most industries that work with data use dashboards. Some industries that use dashboards include healthcare, marketing, human resources, social media, and project management . A social media dashboard can improve a platform’s infrastructure.

What are key performance indicators (KPI)?

KPIs are targets set for companies or professionals to meet to succeed. It provides evidence of performance and progress and helps businesses make crucial plans and decisions.

By Danel Redelinghuys

Danel is from South Africa but currently lives in Cambodia. After teaching English as a foreign language to children in Asia, she started working as a content writer. She is eager to share her love for teaching and learning by helping others find their passions and access the tech industry. Danel’s personal interests include travelling and learning about new cultures.

Sourced from Career Karma

About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.

Read More
By
In News

How to Make A Calendar in Excel: A Step-By-Step Guide

No Comments

By Mercy Ugonna Njoku

While Microsoft offers users an array of calendar templates, learning how to create a calendar in Excel gives you more autonomy over your entire spreadsheet. If you’re interested in mastering Microsoft Excel or at least being able to efficiently use it in your daily tasks, one of the basic tasks you’ll have to learn is how to make a calendar in Excel. This will let you customize your calendar with all the features your specific needs require.

Calendars allow us to better organize all aspects of our lives. By simply learning how to make a calendar in Excel, you can easily streamline your work tasks and meet your deadlines more regularly. This article provides you with a step-by-step guide on how to make a calendar in Excel. We also go over why learning how to make a calendar in Excel can help improve your work performance.

What Is A Calendar in Excel?

A calendar in Excel is a spreadsheet that is made up of customized tables containing the days, weeks, and months of a given year. It looks like a basic calendar layout. Microsoft Excel can be used to prepare an interactive and automatic calendar containing possible events and tasks. An entire calendar’s date range can be compressed into a single spreadsheet or multiple separate worksheets.

You can create a calendar sheet in Excel by using Visual Basic, Microsoft’s programming language for basic coding. You can also use a pivot table to create a calendar grid or insert a premade yearly calendar template. Formatting data in rows and columns to create a calendar from scratch or using power query add-in allows for greater freedom and flexibility in how the data you input is presented to you.

Why Learning How to Make A Calendar in Excel is Useful

  • It Boosts Team Collaboration. Learning how to make a weekly or monthly calendar in Excel provides you with the opportunity to have clearer communication with your teammates. You can easily review each other’s schedules on a daily or weekly calendar in Excel, make adjustments, and leave calendar notes without having to contact any particular teammate directly.
  • It Makes it Easier to Measure Project Progress. Learning how to make custom calendars in Excel helps with project management. Using Excel, you can build a workflow board that details how tasks will be prioritized and tackled over a certain period. You can easily monitor project timelines on the tasks you’ve been assigned and adjust them accordingly based on their completion rates.
  • It’s a Major Requirement for Administrative Roles. If you’re interested in becoming an accountant, secretary, or executive assistant, you need to be able to schedule events and tasks on the go. You’ll likely use a calendar management tool like Microsoft Excel, which comes with impressive features and add-on functions for easy calendar planning.

How to Make A Calendar in Excel: A Step-By-Step Guide

Step 1: Install the Microsoft Excel Application

To make a calendar in Excel, you’ll first need to install the Excel app, which you’ll be using for this project. Make sure you install the newest version, as it comes with upgraded features. Microsoft 365 houses all of Microsoft Office’s apps. If you have an earlier version of the Microsoft Office suite such as Microsoft 2021 or 2019, you’ll still be good to go.

Step 2: Create a New File

You’ll then need to create a file to house your calendar. To create your calendar file, launch the Excel application and go to the “New” button to open a blank workbook. At this point, you have to decide if this is a monthly or yearly calendar.

For this project, we will be creating a yearly calendar. We recommend you save the file as “Calendar 2022” or something similar. Then, you’ll want to create a sheet tab and name it after the month of the year you want your calendar to start on.

Step 3: Input Calendar Dates

First, select the seven cells in the first row and click on “merge and center” to create a title field in the merged column header. Once you’ve done this, input the days of the week in the second row of your worksheet, then count the days of the month serially under the seven rows to produce a table of seven columns by six rows, including the row containing the weekdays.

Step 4: Format the Calendar Cells

Next, you’ll need to format the table to give it a structure as well as proper spacing. This is done by highlighting the seven cells from the numbered cell borders option, then adjusting the width of the cells with the first data using the arrow that turns into a “plus sign” to automatically proportion all the other cells to the same width and height sizes.

Step 5: Align the Date Digits in the Cells

Highlight the columns and rows that contain the dates for the current month, then right-click on “format cells”. Once a box opens up, click on “alignment”. There, you’ll find “text alignment” for horizontal and vertical drop boxes. Select the format type you want. You can select “right or left” for the horizontal and “top or bottom” for the vertical. Click “ok” and input your digits to see the results.

Step 6: Repeat the Same Process for Each Month.

To recreate this process, go to the “Sheet tab” to replicate your calendar grid for the other months. Right-click on the “Sheet1”, which you’ve renamed to the month of your choice in step 2, and select the “move or copy” option. Once a Dialog Box opens up, you’ll see the “move to end” and “create a copy”. Click on “create a copy” and “ok” to replicate it. You can then customize the dates and background colour if you wish.

How to Make A Calendar in Excel at Once

To make a calendar in Excel at once, you need a pre-made calendar template. First, decide if it’s a yearly or monthly calendar template. Open your Excel worksheet and click “New” to access the search field and browse monthly or yearly calendar templates on the web. Download the calendar layout that suits your needs, click “Create” and proceed to customize your calendar.

Benefits of Making A Calendar In Excel

  • Excel Comes with Plenty of Design Features. Excel makes it easy to organize your schedule with functionality and aesthetics. You’ll find features in Excel that allow you to implement automatic colour coding in a calendar grid and easily insert template images from multiple sources into a spreadsheet. You can also adjust cell sizes or customize font sizes and styles to suit your preference.
  • Excel Calendars Are Flexible. There are very few limitations to what your Excel calendar can do for you. You can use it as a printable calendar or an offline calendar, you can use your dynamic calendar to store information by leaving notes in the cells for future reference, or you can use it to track your workflow by assigning schedules for a given time period.
  • Excel Calendars Can Be Shared. Excel calendars can be shared with external users when you save them on the cloud. This is especially beneficial for teammates who need to periodically review their calendar events for work purposes while away from the office. Excel also comes with security features that allow you to restrict external access to your calendar.

Importance of Learning How to Use Excel Sheets

Microsoft Excel is one of the most popular calendar apps available. Many companies have adopted this tool for project management and hiring managers often give preference to candidates who can competently use Excel. With this multipurpose tool, you can perform simple tasks like data entry and complex projects.

How to Make A Calendar In Excel FAQ

How can I create a calendar in Excel?

To create a calendar system in Excel, start by ensuring you have installed and subscribed for Microsoft 365. This gives you access to the updated versions of all the applications. Then, you can decide if you want to create your calendar from scratch or use a premade template. This article contains step-by-step instructions on how to create a calendar from scratch in Excel.

Where is a calendar in Excel?

Excel comes with calendar templates that users can customize to suit their specific project needs. You can find a wide range of calendar templates in Excel by simply selecting the “File Menu” and choosing the “New” button. It will lead you to a search box where you can search through the different types of calendar template options.

How do I add a calendar in Excel without add-ins?

Start by adding the developer ribbon using the “customize ribbon”. Open the “Developer Tab”, click on the “insert” drop-down menu, then “more controls’. Click on the Microsoft Date and Time Picker Control and the cell you want to add the date picker to. Lastly, click on “properties” on the design menu to customize it to your liking. If you’re using the 64-bit Excel version, use an external date picker plugin.

How do I create a calendar from a date in Excel?

Start by choosing the date and writing it in a cell, then go to the home tab to click on the “number menu”. It will give you a drop-down box, from which you’ll choose “date”. From there, click on the “more number format” to select a date format. You can then follow the rest of the steps in this article to finish creating your calendar from scratch.

By Mercy Ugonna Njoku

Mercy, a tech enthusiast from Nigeria, holds a BSc in Business Management from Abia State University. Prior to launching a freelance writing career, she worked in finance as an assistant accountant at a coffee house, where she was sometimes tasked with content creation, sparking her interest in writing. As a writer, she seeks clients with altruistic goals and takes pride in helping others. At Career Karma, Mercy aims to help people navigate unfamiliar and complex situations in their professional lives so they can thrive. Her hobbies include drawing, working on startup projects, and engaging in discussions.

Sourced from Career Karma

Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.

Read More
By
In News

Graphic design apps are now as fundamental as Excel — here’s how non-designers get up to speed

No Comments

By ALEXIS ONG .

In today’s digital landscape, it’s critical to understand why design matters beyond the realm of the creative department. After all, marketing, branding, sales, business collaborations, and partnerships all hinge on the strength of a company’s branding and its consistency.

It’s no longer enough to have a couple of graphic designers to shoulder the burden of this responsibility. In the social media age, a truly successful brand needs seamless harmony across different platforms, whether it’s in traditional advertising, communications, or customer service. It’s not just about engaging aesthetics, but ensuring that anyone who needs to work with the brand image can do so quickly and effectively. Not only does it save time and money, but everyone can stay on the same page with a greater sense of productivity.

We spoke with Adobe’s Principal Solutions Consultant, Bart Van de Wiele, who helps companies navigate rapidly-changing digital media through Adobe’s formidable suite of creative solutions. With a long background in graphic design, years of experience in software training, and a deep knowledge of the Adobe ecosystem, Van de Wiele is well-equipped to explain why basic design tools are a must-have resource for everybody.

Design is for everyone 

You’d be surprised at how often non-designers could use design to make their jobs more effective.

“Imagine you’re a business manager, and you have an idea of how you think you can improve a certain business process internally,” says Van de Wiele. “And imagine that you’re gathering a meeting and you need 15-20 minutes to quickly convey your point — and then all of a sudden, you think of a better way to visualize your idea. You have access to an infinite amount of images that can help you support an idea or a metaphor — anything that you might have in your head.”

In reality, a business manager or marketer might not have access to a brand’s most up-to-date logo or color palette, and a graphic designer won’t always be available to help. Even something like proper typography could make a difference in bringing confidence and polish to a new proposal.

Citing a survey by Forrester, Van de Wiele believes that creativity should be a critical part of any business philosophy. “More than 82% of the people, the decision-makers, the executives that they interviewed, believe that a company can gain larger business benefits, like revenue growth or market share, if the employees have a more creative mindset,” he tells TNW.

Creativity isn’t just about developing a business’s logo or graphics — but about finding better and more effective ways to help non-designers succeed.

Getting started 

Creativity can be a daunting task, especially for those who lack experience. For Van de Wiele, Adobe offers a convenient way for non-designers to get their feet wet — newcomers can start with a free Adobe ID to access services like Adobe Spark for building social media graphics or start collaborating with existing designers.

“The actual designer can create a dedicated library for you,” he explains. “Imagine you’re in marketing. What I can do is create a library of product shoots, brand colors, brand styles, maybe the logos, the icons, or anything else you might need.” And no need to worry about non-designers tampering with precious files — you can set read-only access to an up-to-date database without risk of altering or removing the files.

“It’s a bit like following a Spotify playlist,” Van de Wiele explains. “If you like it, you have to subscribe to it. If the owner of the playlist adds a few extra songs, you’ll see that reflected on the updated playlist.”

You can also apply your new Adobe library to Office 365 — a handy feature for people working in PowerPoint and Word who want an exact match for paragraph styles, company colors, and text properties.

The next step up is Adobe Spark, a free solution with premium features. Van de Wiele describes it as an all-round tool to create anything you might need — webpages, video, social media graphics — with minimal fuss. Besides providing preset graphics dimensions for online platforms and built-in collaboration tools for marketers and entrepreneurs, Spark is, according to Van de Wiele, “easier than Powerpoint.” All you need to know is how to move text around, and place and crop images.

Not only does this streamline workflow by improving accessibility, but it also empowers all non-designers in the company to do simple design-related work on their own.

Smarter searching

Finding the right image is a critical part of good design, but finding it painlessly is another story. As visual imagery is such a powerful part of media today, Adobe Stock has developed more intuitive tools to improve its search functions.

“It doesn’t matter how big your stock imagery database is,” Van de Wiele points out. “If I type in a word — guitar, surfer, dog under an umbrella, whatever — if that returns me 5 million hits, how is that going to help me?”

Adobe Stock allows you to click on elements of a photo — like a dog or umbrella — and reposition them as you need. Being able to move the dog to the right of the frame and search for photos with a similar composition is a simple but revolutionary step towards better efficiency.

“You’re sketching up your idea, basically,” he says. “That is really going to shorten the production time that you have, or that you need in order to accomplish something like that. And that’s just available to everyone.”

All roads lead to Photoshop

Van de Wiele also encourages everyone to try Photoshop, but to start with the basics.

“First learn how to crop, resize an image, those basic things,” he suggests. He also recommends getting to know Camera Raw, “a filter in Photoshop which gives you access to basically any image optimization feature to give your pictures a professional look.”

With 18 years of experience under his belt, Van de Wiele observes that while technologies have changed our methods of design, “what has not changed is how a designer is briefed on an assignment, how work is sent for review, and how design assets are shared and stored.”

Adobe’s integrated ecosystem drags outdated briefing processes and resource management into the 21st century. “For design, specifically, 40% of the creative leads that we’ve spoken to in the past have told us that outdated workflows are slowing down their core processes,” he says. Providing greater accessibility and usability for non-designers creates immediate material benefits for employees.

Bringing non-designers in sync with design work streamlines our ability to tell better visual stories. “Basically, you have to look at graphic design as something that’s not a product on its own, but something that emphasizes your message,” Van de Wiele says. “It’s a means to help you communicate and get your message across. And that’s the thing, it basically all comes down to communication.”

Feature Image Credit: Moose

By ALEXIS ONG

Sourced from TNW

Read More
By
In News

Power BI Desktop Update Brings Excel Users Into the Fold

No Comments

By:

The August Power BI update allows Excel users to import their work into Microsoft’ cloud-powered business intelligence tool.

Microsoft wants to help business users unearth insights that may be locked away in Excel workbooks with the latest update to Power BI Desktop.

Formerly called Power BI Designer, Power BI Desktop is the free data exploration and interactive reporting tool that enables users to run analytics on various sources of business data, including Google Analytics, SQL Server and Salesforce, to name a few. Now users have another option: Excel.

The new import capability “makes it possible for users to convert their Excel Workbooks containing Power Query queries, Power Pivot models and Power View worksheets into a Power BI Desktop file,” announced Miguel Llopis, program manager for Microsoft Power BI, in a company blog post. “This is a one-time operation to help users get started in Power BI Desktop with their existing reports.”

Llopis characterized the Excel import feature as a stepping stone to business analytics until his company inevitably improves how the two platforms share data. “While we plan to have other means of communication (import/export) between Excel and Power BI Desktop in the future, the current feature allows existing Excel users to get started with Power BI Desktop.”

There are limits to the types of Excel data that can be imported to Power BI, according to a Microsoft support document, he cautioned. They include SQL Server Analysis Services Tabular models, the KPI (key performance indicator) Data Model objects and binary data columns, among a handful of others.

The August update also includes connectors for HDInsight Spark, the company’s Azure-backed, open-source big data analytics solution, and Azure SQL Data Warehouse. Plus, the product now supports custom Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX) queries from SQL Server Analysis Services Database.

In addition to those new behind-the-scenes additions, Microsoft has made some improvements to Power BI Desktop’s Navigator, Query Editor and data modeling tools. For instance, users can “resize the Navigator dialog, so that they can easily preview tables with lots of columns” and use shortcuts to select multiple items, said Llopis.

On the data modeling front, users can resize columns or double-click on a column’s border to auto-adjust. “Users can now easily move measures from one table to another, without having to recreate the measure in the destination table,” Llopis added. They can “select a measure and use the ‘Home Table’ option in the Data Tools – Modeling tab in the ribbon,” he instructed.

Finally, Power BI Desktop users have a little more flexibility in selecting their Live Analysis Services data sources.

“Based on feedback from many of you, we have improved the Edit Queries dialog for Live Analysis Services connections to allow users to modify the database and model, in addition to the server (which was available in our previous release),” Llopis said. “After confirming the Server name and clicking OK, users are presented with the Navigator dialog where they can browse to the right Database and Model, just like they were able to do on the initial connection.”

By:

Pedro Hernandez is a contributor to eWEEK and the IT Business Edge Network, the network for technology professionals. Previously, he served as a managing editor for the Internet.com network of… V

Sourced from eWeek

Read More
By