When it comes to Excel, this is a simple concept to live by: If you find yourself doing it manually, there is a more straightforward way to do it.

When you want to delete duplicates or do simple calculations or sort your results, you will find a way to do it almost always with a single click (or two).

Yet it’s easy to overlook those shortcuts if you’re not a power user. Even before you know it, you can take a ton of your time to organize an alphabetical list of names.

A Few Basics

You will see a grid with the columns and numbered rows when you open Excel. You may insert data in the form of numerals, text, or (most important) formulas into the spreadsheet cells that perform data calculations in other cells.
You’ll see file folder tabs near the bottom left corner of the page representing a spreadsheet in a broader “workbook.” (Excel allows you to do calculations in several workbook spreadsheets).

Sorting data

The ability to organize information is another beneficial feature in Excel. Excel allows you to sort up to three criteria at a time by the priorities that you set (for example, first sort the rows by column B, then column A, then column E). Excel would help you organize the list according to one or more of those criteria if you had a list of your patients with name, gender, and age.

Functions and Formulas

One of Excel’s best qualities is that the software makes it very easy to use formulas. Hundreds of formulas can be entered by clicking on the Formulae Wizard button on the main toolbar, including the simple sums, averages, and complex statistical calculations. Excel groups its functions into classes such as financial, mathematical, logical and “most recent”

The wizard will show you two lists: a list of groups of functions in the left-hand window, and a list of functions in the right-hand window in the same category. For example, if you want the average function, select Statistics in the category list to display the available statistical functions. Then in the function list, click on AVERAGE and click on the Next button. The wizard screen for the feature Average appears

Users could insert a series of numbers in the given cells, and the wizard would determine the average. You already have the numbers on the spreadsheet in this case, so you just need to let Excel know which cells you wish to have in the Average function by clicking the cell A1 and left click while dragging it to other cells.

The selection will appear automatically on the wizard screen, and in the cell, you highlighted—Click End to find average once you have highlighted all the cells. The beauty of the spreadsheet is that the result will change accordingly, as you change the value of any cell included in the formula. Just give it a try!

Graphing

Excel lets you present and manipulate your data. The Chart Wizard enables you to pick from 15 different types of graphs (each with several versions) and help you mark the data and the chart itself.

Whether you’re building an essential phone directory for patients or monitoring data for a disease management system, Microsoft Excel will impress you. Yet it’s also easy to use, so beginner doesn’t get lost or frustrated.

For help with Microsoft Excel, schedule remote training sessions.

There are three ways to make a calendar in MS Excel.

1. Using Microsoft codes
2. Creating a calendar manually
3. Using a calendar template

Let’s take a look at these steps in detail.

1. Using Microsoft codes

Microsoft provides programming language to help create a calendar in Excel. But you need to use at least Microsoft Office 2007 or the latest version to make this work. Here are the steps you should follow:

• Start a new workbook.
• You will see a Developer option on the ribbon on top. Select Visual Basic.
• There is an Insert option in the menu. Select Module from there.
• You will find an Applications Code in Visual Basic. Copy that code into your module sheet.
• Go to File menu and click on Close, followed by Return to Microsoft Excel.
• Select Sheet1 tab from the bottom left corner.
• Select Macros from the Developer ribbon and go to CalendarMaker. Select Run. This finalizes the calendar.
• Add a title and you have the calendar ready.

2. Creating a calendar manually

This is a slightly lengthier process but once you finish it, you will learn a lot about Excel. Here’s what you need to do:

• Create a new worksheet and highlight the columns A1 to G7.
• Use the Home tab to format the columns’ width. Start by selecting the Cell option and then Format Column Width, followed by typing 18 in its dialogue box.
• You should also format the rows’ height. Follow the same steps as above. Select Format Row Height and type 75 in the dialogue box. For A1, the height should be 118. For A2, change the height to 30.
• Now you should set the margins and orientation. Highlight A1 to G7. Select Landscape from Page Orientation. Also, select Custom Margins and enter .25 for each box. Follow the same pattern for the left and right field boxes. Put 0 in the Header and Footer boxes. Select Vertically and Horizontally.
• Highlight A2 to G7 to define the calendar’s page setup and border. Select All Borders from the Border section. Then type the days of the week from A2 to G2. Make sure you center them vertically and horizontally. Now highlight A1 to G1 and select Merge and Center.
• Add a title. You now have the calendar ready to use.

3. Using a calendar template

You can use a calendar template if you don’t want to create a calendar manually.

• Start a new worksheet and go to File. Select New. When you see the search field, type Calendar. Choose from a monthly or yearly calendar from the templates.

• Once you choose the template, a table would appear with months, year, and first day of the week. Modify these cells if you want to customize the calendar.

• Give a title to your calendar by double-clicking on the title option.

• Congrats! You now have a brand new calendar on Excel.

Creating a calendar on Excel isn’t a tough task. For more information on all things Excel from a professional source, visit excelhelp.com/blog.

Microsoft Excel has come in handy for a lot of folks. Accountants, engineers, business financial managers, mathematicians, actuaries and even civilians who use the program for its proficiency in common calculations. In 1996 the program was used by 30 million people, a number that has possibly doubled or tripled in the last century. The excel team announced 6 new functions deemed to make work easier for excel users by building custom functions that were previously built by users, taking a lot of their time and energy. Here are the 6 new functions that have been added to this formula program for texts and numbers on sheets!

CONCAT Function
The new CONCAT function is developed from the CONCATENATE function. Sometimes you may need to amalgamate data on a long list of cells with another list. With CONCATENATE, you probably were doing each cell individually, which takes a lot of time. This new CONCAT function allows you to combine two long lists at ones say A1:A30 and B1:B30. Whether you want to combine strings, texts or ranges, this function allows you to combine all into a single string, all you have to do select the range of cells you intend to combine and use CONCAT function. CONCAT(text1,[text2],…) will have a range of rows/columns each with different words that give you a complete sentence from the individual words. You, however, need to specify the spacing in the formula as this function does not come with the delimiter argument.

TEXTJOIN Function
It is similar to the CONCAT, as the word suggests but tends to even be more powerful. This function allows you to combine multiple ranges or cells into one string, but in this case with a specified delimiter. Unlike the CONCAT function, you can now specify the separator in the function. This delimiter is considered a separator in your text, it could be a space or otherwise and is filled in as quotation marks (“”). Note that selected cells have a 252 limit. In an example: TEXTJOIN(delimiter, ignore_empty, text1, [text2], …). As seen in the formula, empty cells can be ignored in the selected range when TRUE or added when FALSE. Since this is a text function, numbers will also be treated as texts.

MINIFS And MAXIFS Function
Based on specified conditions, these two new functions return the maximum value-MAXIFS and minimum values-MINIFS. Multiple conditions that are fed into the formula make the MAXIFS function detect the maximum value as shown: MAXIFS(max_range, criteria_range3, criteria3, [criteria_range4, criteria4],…)the range is the wider group from which the maximum value is selected. The criteria are the conditions from which these values are being evaluated. The opposite is true for the MINIFS function. They can be used on dates texts and numbers. You should make sure that all ranges, the max, min, and criteria, are of similar sizes lest you get an error message.

IFS Function
This is a solution to users who type long-nested IF formulas, especially those that have no idea what INDEX MATCH is. This function assesses several conditions and yields the value that corresponds to this. As such it is used by: IFS(logical_test1,value_if_true1,[logical_test2, value_if_true2], …). This also has a limit of 127 conditions/logic.

SWITCH Function
It supplements the IF function since it allows you to assess your expression against different scenarios just like the IF function. The arguments are then compared. In a formula example: SWITCH (expression, value1, result1, [value2, result2], …, [default]). It, however, does not work with logical operators.

With this new knowledge, we hope your calculations have just been made easier and faster! If you’re still looking for more guidance, schedule remote Excel training for you or your team members!

The Top 5 Excel 2019 Features

With the new release of Microsoft 2019, Microsoft Excel 2019 just got updated with new features and upgrades. But which features of Excel 2019 matter to you? Which ones will actually help you do your job better?

There are 5 key new features that just jump off the edge. However, for those that use Microsoft 365, all these features exist in Excel 365. The top 5 excel 2019 features are as follows.

1. Improved Data Visualization with Insights
   While this is a simplified generalization, there are two kinds of Excel users: those who are well aware of the kind of data they are looking at and the best charts to visualize it with; and the rest of us, who know what they’d want to see but don’t know too well how to get there.

With Excel Insights, a new feature on the Ribbon, you get lots of suggestions on great visual charts to see your data. Some of these charts you’ve come across or seen before, and some are totally new ideas. To use the Insights features, you have to click on a cell in an existing range of data, and then click on Insert, then Insights.

This will open a task pane on the right part of the window with some new visual presentation ideas for your data. You can click the plus sign “+” that appears on each suggestion to use on your Excel file.

1. 2D and Funnel Map Charts
   A funnel chart is often used to illustrate the different states in a process. For instance, if you want to see the amount of closed sales compared to the amount of subsequent leads, you can use a funnel chart to visualize this.

A process with a 100% value with some potential drop-off points along the process can work great with funnel charts. With the 2019 update, Excel can now support funnel chart type natively, along with map charts for geographical data.

To use funnel charts, the data used should be arranged in such a way that you have at least one column listing the likely stages in the process, and a separate column for the values. The click on Insert, Waterfall, Stock, Funnel, Radar, or Surface Chart, and the choose Funnel. If you’re using a map chart, ensure that the geographical data is filled and then click on Insert, then Filled Map.

1. Automatic Data Analysis with New Ideas
   The all new Ideas feature in Excel 2019 highlights the power of artificial intelligence. With it, you can select a range of data and then click Insert, then Ideas and have Excel analyze your data automatically for any trends and patterns. When the analysis is complete, Excel will provide you with several pre-built PivotCharts to incorporate in your workbook.
2. External Data Navigator
   Excel 2019 allows for a much easier way to work with any external data that you’d like to import to one of your worksheets with the addition of the all new Navigator dialogue box. When you import data or perform queries with external data sources, the Navigator box will let you preview the data and specify how and where to download them.
3. New Functions
   The new version of Excel introduces six new functions that many users will find useful. These include:

• CONCAT

• TEXTJOIN

• MAXIFS

• SWITCH

• IFS

• MINIFS

For instance, the new IFS function lets you write sophisticated formulas that would previously require multiple IF functions nested in each other. This leads to simpler, shorter formulas that helps you work more efficiently with fewer errors.

There are many different uses for Microsoft Office, and one powerful potential feature that many people don’t utilize is the ability to create a custom calendar using Microsoft Excel.

Step #1: Start with the File tab on the right hand side and then click New

You will see a variety of options pop up and you may notice there are some calendar template options. While these can be useful in some situations, they don’t have quite the ability to customize as we’re looking for so you’ll actually want to click the Blank Workbook option (should be the very first one in the upper left-hand corner).

Step #2: Add weekday headers

Start on the second row of the spreadsheet. The first row will be used for something else, and we’ll get to that in just a moment. But start on row two and type in the days of the week – one per column. So Sunday in A, Monday in B, etc.

Once all seven have been typed in highlight all the weekdays you type in, go to the Home tab, then click format in the cells group. You then want to hit Column Width and set the width to 20. This guarantees enough space for each day and to create your future scheduling.

Bold the names of the weekdays if you prefer, otherwise that’s good for this portion.

Step #3: Calendar title

This is where the first blank row comes back into play. You will want to add the current month as the title of the calendar. Use the formula =TODAY(). After this, you’ll fix the format by going to the Home tab, hitting the Number group, and then hit the date drop down. Under More Number Formats you can then adjust the month format.

If you’re picky about making sure it is centered, highlight all the cells in row one and go to the Home tab where you will then want to click on “Merge & Center.” This will center the month.

Step #4: Building out the days

Creating the full calendar setup means you need to create solid borders to clearly distinguish the days from one another. Do this by highlighting the entire spreadsheet (title/header row included). Once you’ve done this you need to go to the Home tab and click the paint bucket that is underneath the Font size number. Select white as the background.

Highlight six cells under the Sunday header. Making sure they stay highlighted, you then want to go to the Home tab and click the borders icon before selecting the option for “Outside Borders.” You should see a solid border line around all six cells in that column.

Highlight that box, then copy and past under every other listed day header. As you copy this under each day the beginnings of a calendar should appear.

Step #5: Create the weeks

Once you have a single week of bordered days from copy and pasting the firs bordered day you then copy and create six weeks.

Step #6: Fill in the dates

Place 1 in the correct box depending on what day of the week the month starts on. Once you have two side by side, drag across and the numbers should fill in the rest of the week. Put a number in on Sunday the next week and repeat and you should be able to quickly fill out all the dates to the 30th or 31st.

That’s the monthly calendar. Copy and paste on 11 other sheets and adjust the starting date to create a yearly calendar. It’s just that easy!

Creating a graph is a lot easier than what you imagined when you have a computer and Excel. However, what you will find is when you are looking at Excel you will find that it has several different graph features. This is when you should know more about the top 5 Excel graph features you can use. By knowing about these features it will be easier for you to find the best ones to use and know you are going to create a graph that is going to be out of this world amazing.

1. Recommended Charts

This is something that you are going to like because it will help you in figuring out which type of chart you will want to look for. This makes it quite a bit easier for you to create the graphs you want to have, but also makes it easier for you to know which one of these is going to look best for your needs. So instead of trying to shove everything into a pie chart, you will finally be able to get the chart done in the proper format.

2. Chart Filters

This is a filter that you will like because it allows you to go out and filter some of the results out. This makes it easier for you to focus on a specific item in the chart. It also helps you in getting to enjoy the fact that you can finally have a great looking chart that is going to have some of the filters done that you want to have.

3. Individual Formatting For The Panes

When you are creating a graph or a chart you will notice that you are going to have some information that you need to have properly formatted for your needs. Since this is the case, you need to make sure you know that inside of Excel you will have a chance to format each of the panes. This makes it easier for you to have a great looking pane that provides the information you need to have.

4. Easier Organization

This is an aspect that you need to consider because if your graphs are not organized they are going to look horrible and no one will want to use them. However, with the new features you will find it is going to be very easy for you to have the information organized like you want to have it organized and know it will provide a good flow to your information.

5. Combo Charts Are Easy

Often you will find that when you are going to make a chart you can get your point across with more than one chart. The problem is it can be difficult to get the two charts on the same output. This is when you will love the fact that you can use a combo chart to get the output that you want to have.

Finding out about the chart features in Excel it will be easy for you to have a great looking graph. Then you will not have to be concerned about the graph not looking great when you are creating one of these charts.