I often get asked by my boss to take the data she accumulates and put it into easy-to-read diagrams and charts. I was using Excel until just recently, which is great, but I needed something with more functionality and design choices. Then I found Microsoft Visio. I think it’s one of the best options available today to create diagrams and flowcharts. I have access to so many great templates and shapes that give my work the professional look my boss requires.
I really didn’t know much about Visio until a colleague told me about it. And when I gave it a try, I was sold. Now my diagrams can be as simple or complicated as I want. Visio provides all the tools and functions I need and comes with a wide variety of built-in shapes, stencils, and objects. I can even create my own shapes and import them if I want.
Have you heard about Visio Online?Microsoft Visio 2016 Viewer lets you view Visio drawings inside your Microsoft Internet Explorer Web browser. It’s a web-based version of Visio. So, when I’m away from the office I can still use it to design, create, edit and share diagrams and flowcharts online. I just upload my Visio diagrams and flowcharts to either SharePoint or OneDrive for Business and edit them in my browser.
Now I can collaborate with my team to streamline projects and work with them on diagrams right from my web browser. And I don’t have to worry about security. The only people who have access to my charts are the ones I authorize. I can even review their comments and add my own directly from my browser. And here’s another plus!—I can store all my diagrams in our OneDrive cloud storage that has 2GB of space.
What my team and I like best about MS Visio is that it’s so easy to use. It’s simple to create top-notch diagrams with commonly-used diagram types and rich shape sets. It’s easy to collaborate with team members and stakeholders, view and add comments, and share the diagram with others. We can pull external information into Vision such as an Excel sheet, or Access database. Now when my boss sends tons of data to me, I pull up my Visio, Excel or Access and design fantastic diagrams in just an hour or so. (She thinks I work all weekend to get this done!)
In case you didn’t know, Visio 2016 was released in September 2015 along with Microsoft Office 2016. It has dozens of templates you can use for a multitude of industries and verticals.
A few new features were added such the ability to connect to Excel data, information rights management for your Visio files, modern and detailed shapes for site plans and floor plans, IEEE-compliant electrical diagrams and home plans (architects, contractors, engineers, and designers will like these), and even 3D map diagrams. Plus, it comes with a bunch of new starter diagrams, themes, and built-in shapes–Hundreds of them! All these shapes are categorized, so they’re easy to find and choose from.
Want to know some really helpful Visio tips? I’ll share them with you here. I now consider myself a Visio expert, and I know there are a lot of beginners out there who might appreciate them.
Text Editing
Sometimes you need to edit text when you’re putting everything together, and not all applications let you do this. However, it’s easy to do in Microsoft Visio: Just click on the shape next to your text and press the F2 button. That’s it. Now you can edit as you wish–Simple! When you’re finished just press “Esc” to get out of the text-edit mode.
Shortcuts
F1 -for Help
Tab key to switch between shapes
Crtl+1– for the “selector” cursor
Ctrl+2 – for the text tool
Crtl+3– for connector lines
Alt for the main toolbar.
F3 for the Format Shape task pane
Crtl+PageUpor PageDown to move between sheets
Ctrl+ scroll up or down with the mouse to zoom in and out
Ctrl+click on and drag an object to copy and paste it in another place.
Draw Shapes
The Drawing Tool is next to the Pointer Tool on the Ribbon. Click on the arrow and select a shape from the drop-down menu. Then you can start drawing your shape. Try combining shapes with the Pencil Tool to make more complex or intricate shapes. You’ll be an expert before you know it.
Save Shapes
If you want to save a shape that you made, Visio lets you do this. Look on the left of the toolbar for “More Shapes” > “New Stencil” and drag and drop your shape into the blank space. Then right-click it and choose “Save As.” Now, just rename the shape and save it.
Add Files
With Visio, you can copy anything from other Microsoft Office apps and paste it into your diagram or flowchart. So, if you have an Excel table or diagram, just copy and paste it right into your Visio diagram or flowchart. This saves you from having to redraw it. You can now proceed with Visio’s editing tools.
Create Flow Charts Quickly in Visio Online.
Choose the Basic Flowchart diagram.
Choose a shape from the Shapes Panel and drag/drop it into your canvas.
Hold your pointer over the shape until you see the Auto-connect arrows.
Move the pointer to one of the Auto-connect arrows. You should see Quick Shapes where you can choose the shape you want to add.
If you want to add some text, double-click the shape.
You can add more shapes by dragging and dropping them from the Quick Shapes list.
Use the smart guides to align your shapes the way you want.
Before long, you’ll be an expert in Microsoft Visio. I hope this helps, and you get the kudos from your boss that I did from mine!
I just read this warning: “Don’t overcharge your mobile phone. Make sure you unplug it from the charger after it reaches 100%. Don’t leave it charging overnight.”
Why is this?
It’s because your mobile phone charger doesn’t stop charging after your phone reaches 100% capacity. It keeps topping off the charge during the night. This is called a “trickle charge.”
While you’re sleeping, and the phone is plugged in, it works to keep fully charged by compensating for the small amount of charge it loses by just being turned on. This is bad because the trickle charge causes your mobile phone to retain a higher ambient temperature than it should. This ultimately reduces the battery’s capacity.
I wondered why I couldn’t seem to keep my phone charged all day. It’s barely three-years-old, and I’m already having battery issues! Now I know why. I’ve been damaging the battery all this time by charging it at night while I sleep. I wish I had known this before!
Mobile phones contain a rechargeable lithium-ion (or Li-ion) battery that charges faster than traditional rechargeable batteries. So, when we plug our iPhone or Android into a charger, it can get fully charged in just about two hours.
By keeping our phones charged overnight we’re increasing the amount of time it spends on the charger, thereby degrading its battery capacity that much sooner.
Hatem Zeine, the founder of Ossia, a developer of wireless charging technology, tells us: “If you think about it, charging your phone while you’re sleeping results in the phone being on the charger for 3-4 months a year. So even though the manufacturers try their best to cover this scenario, this process inevitably lowers the capacity of your phone’s battery.”
Batteries decay from the moment you start using your new phone. This means they gradually lose their ability to hold a charge. By charging your phone overnight, you’re increasing the amount of time it spends with the charger. As a result, it degrades the capacity much sooner.
If you’re like me, you’re always on your phone checking text messages, emails, calling people, listening to music, watching videos, surfing the Web and more. It’s no wonder the battery runs down so quickly. However, if we’re careful about the way we recharge our phones, we can get much more life from the battery.
The people at Cadex Electronics that make lithium phone batteries say:
“Go ahead and charge to 100%. There’s no need to worry about overcharging as modern devices will terminate the charge correctly at the appropriate voltage…Modern smartphones are smart, meaning that they have built-in protection chips that will safeguard the phone from taking in more charge than what it should. Good quality chargers also have protection chips that prevent the charger from releasing more power than what’s needed. For example, when the battery reaches 100%, the protection hardware inside the phone will stop current from coming in, and the charger will turn off.”
However, they go on to say:
“Li-ion does not need to be fully charged as is the case with lead acid, nor is it desirable to do so. In fact, it is better not to fully charge because a high voltage stresses the battery.”
Don’t wait until your phone battery gets to 0% to charge it. A good time to charge it is when the battery reaches 35-45%. Doing this will help to preserve the battery life. If you do this religiously, then you should be able to keep your smartphone for longer than two years.
So essentially what I’ve learned is that rechargeable batteries are doomed to failure. They are constantly decaying from the moment you first turn on your brand-new phone and eventually lose their capacity to hold a charge. How depressing!
This is why my phone keeps losing its charge more quickly the longer I have it! I’ve owned my current iPhone 7 for over two years, and I’ve experienced a significant reduction in battery capacity.
Even worse, Apple tells us that constantly charging and recharging the iPhone battery isn’t good because the capacity of Li-ion batteries diminishes slightly with each charging cycle.
Most Android phones have a feature that allows for fast charging. They also have a chip with a Power Management IC (PMIC) that tells the charger when it’s receiving the higher-voltage fast charging to prevent it from overheating. Heat is a bad thing for all mobile phones. This is why you should never leave your phone in a hot car. The same goes for freezing temperatures.
So, what do the experts advise us to do? How should we charge our smartphones?
Here’s what the people at Cadex say to do:
“Don’t wait until your phone gets close to a 0% battery charge until you recharge it. Full discharges wear out the battery sooner than do partial discharges. Wait until your phone gets down to around a 35% or 40% charge and then plug it into a charger. That will help preserve the capacity of the battery. You should also keep your phone cool, as higher temperatures accelerate the loss of battery capacity.”
Another tip: Take off your phone’s case before you charge it so it won’t overheat.
Well, I guess this is the reason why I never seem to keep a phone longer than two years. This, coupled with the fact that Apple keeps coming out with cool new phones entices me to replace my old one. I know–This can be an expensive proposition. Maybe this is what the phone companies planned for all along–To keep us buying new phones! And with the pay-by-the-month plans that providers now offer, they make it even easier to swap out our not-so-old phones for a new one.
But for those of you who want to keep your phones for longer than two years, charge your phone during the day after it reaches 35 to 40 percent and unplug it when it reaches 100%. This might get you more longevity from your Li-ion battery so you can hang onto your phone for another year or so.
You probably use a number of personal identification numbers (PINs), passwords, and passphrases to get money from ATMs, to use your debit card when shopping, or to log in to your personal or business email. Hackers represent a real threat to both your personal and business password security and confidential information. Now, these criminals are using a technique called Password Spraying to steal your information.
According to information derived from FBI investigations, malicious cyber actors are increasingly using password spraying against organizations in the United States and abroad. In February 2018, the Department of Justice in the Southern District of New York indicted nine Iranian nationals, who were associated with the Mabna Institute, for computer intrusion offenses. However, password spraying isn’t limited to this group. Other hackers are using it to gain access to both personal and business confidential information.
Manhattan U.S. Attorney Geoffrey S. Berman said: “Today, in one of the largest state-sponsored hacking campaigns ever prosecuted by the Department of Justice, we have unmasked criminals who normally hide behind the ones and zeros of computer code. As alleged, this massive and brazen cyber-assault on the computer systems of hundreds of universities in 22 countries, including the United States, and dozens of private sector companies and governmental organizations was conducted on behalf of Iran’s Islamic Revolutionary Guard. The hackers targeted innovations and intellectual property from our country’s greatest minds. These defendants are now fugitives from American justice, no longer free to travel outside Iran without risk of arrest. The only way they will see the outside world is through their computer screens, but stripped of their greatest asset – anonymity.”
How Does Password Spraying Work?
Password spraying is a type of brute force attack where hackers use a username with multiple passwords to gain access to your IT system. With traditional brute force attacks, the criminal uses one username with multiple passwords. Employing a lockout functionality, which locks the criminal out after a set number of login attempts, is an effective means of dealing with traditional brute force attacks.
However, with a password-spray attack (also known as the “low-and-slow” method), the malicious cyber actors use a single password against many accounts before moving on to another password. They continue this process until they find one that works. This strategy works for them because they can avoid account lockouts. It circumvents lockout functionality by using the most common passwords against multiple user accounts until they find one that works.
Password spraying targets single sign-on (SSO) and cloud-based applications using federated authentication. A federated authentication identity provides single access to multiple systems across different enterprises. Criminals target federated authentication protocols because it disguises their activities and ensures their anonymity.
Attackers use password spraying in environments that don’t use multi-factor authentication (MFA), rely on easy-to-guess passwords, or use SSO with a federated authentication method.
Your Email Is Also At Risk
Hackers also prey on email accounts that use inbox synchronization (which pulls emails from the Cloud to inboxes on remote devices). Malicious actors use inbox synchronization to obtain unauthorized access to your organization’s email directly from the Cloud. Then they download email to locally stored files, identify your company’s email address list, and secretly apply inbox rules to forward your sent and received messages to them.
The United States Computer Emergency Readiness Team (US-CERT) details how hackers use password spraying, what you should watch out for, who is at risk, and the impact this type of attack can have on your organization.
Your Technology Service Provider can explain this to you and your employees in plain language, and help you protect your organization against password spraying and other attacks.
Traditional Tactics Techniques & Procedures
Using social engineering tactics to perform online research (i.e., Google search, LinkedIn, etc.) to identify target organizations and specific user accounts for initial password spray
Using easy-to-guess passwords (e.g., “Winter2018”, “Password123!”) and publicly available tools, execute a password spray attack against targeted accounts by utilizing the identified SSO or web-based application and federated authentication method
Leveraging the initial group of compromised accounts, downloading the Global Address List (GAL) from a target’s email client, and performing a larger password spray against legitimate accounts
Using the compromised access, attempting to expand laterally (e.g., via Remote Desktop Protocol) within the network, and performing mass data exfiltration using File Transfer Protocol tools such as FileZilla
Indicators That You’ve Been Attacked
A massive spike in attempted logins against the enterprise SSO portal or web-based application;
Using automated tools, malicious actors attempt thousands of logons, in rapid succession, against multiple user accounts at a victim enterprise, originating from a single IP address and computer (e.g., a common User Agent String).
Attacks have been seen to run for over two hours.
Employee logins from IP addresses resolving to locations inconsistent with their normal locations.
Typical Victim Environment
The vast majority of known password spray victims share some of the following characteristics:
Use SSO or web-based applications with the federated authentication method
Use inbox synchronization, allowing email to be pulled from cloud environments to remote devices
Allow email forwarding to be set up at the user level
Limited logging setup creating difficulty during post-event investigations
The Impact
A successful network intrusion can have severe impacts, particularly if the compromise becomes public and sensitive information is exposed. Possible impacts include:
Temporary or permanent loss of sensitive or proprietary information;
Disruption of regular operations;
Financial losses incurred to restore systems and files; and
Potential harm to an organization’s reputation.
7 Steps You Can Take To Mitigate Password Spraying Attacks
Enable MFA and review MFA settings to ensure coverage overall active, internet facing protocols.
Review password policies to ensure they align with the latest NIST guidelines and deter the use of easy-to-guess passwords.
Review IT helpdesk password management related to initial passwords, password resets for user lockouts, and shared accounts. IT helpdesk password procedures may not align with company policy, creating an exploitable security gap.
Many companies offer additional assistance and tools that can help detect and prevent password spray attacks, such as the
Make sure your employees change their corporate passwords every 60 days.
Establish a password policy that prohibits easy-to-guess passwords. Enable multi-factor authentication (MFA) for all web-based applications. If MFA practice is already in place, review current protocols thoroughly to ensure it is maintained well
Ask your Technology Solutions Provider to conduct Security Awareness Training for your employees at all levels.
The FBI Reporting Notice
The FBI would like you to report any suspicious or criminal activity to your FBI field office or the FBI’s 24/7 Cyber Watch (CyWatch). Field office contacts can be identified at www.fbi.gov/contact-us/field. CyWatch can be contacted by phone at (855) 292-3937 or by e-mail at [email protected].
Today’s internet has literally changed the way businesses function. It has redrawn the boundaries and changed the rules of competition in today’s business climate. The Internet has allowed business owners to create much closer relationships with their customers. In addition, it has given consumers more power through access to unlimited information.
Regardless of the size of your business, you probably have a website and a pretty decent one at that. In fact, websites are not as costly as they once were to build and manage. Thanks to the increase in the number of web developers and online sites that host websites, almost anyone can have a website if they want it.
For savvy business owners, their website is the key to greater sales and stronger relationships with customers. A successful website is constantly adding new content that offers value to consumers. They update the site regularly with the latest security and technology. A company with an amazing website almost always excels in the marketplace over competitors who don’t. Why? Because they understand that their business website is the voice of their company online.
Websites have become a valuable commodity to business owners. They are the first stop of prospective clients who want to find more information about your business. Current clients also rely on websites to find product information and updates. Your website is the place where all the action takes place each day. Your website can make or break your business. Knowing how important websites are, most business owners are interested in learning how they can get the most out of their site.
Allow your website to communicate your story to clients
With the understanding that websites do speak to your customers, you should ask yourself the question, “What do you want your website to say to customers?”
Each day, you must make sure that the information you provide is important to your customers and that it’s accurate. Does it resonate well with them? Will it make them want to purchase your products or services? Image is everything in a business. Since your website represents the first image that people have concerning your business, it’s important to make a good first impression. As it is commonly said, first impressions matter. What story do you want the website to tell?
The best websites always tell a compelling story. Sometimes your story is all about why you started your business and sometimes it’s more about your roots, your background. Great businesses have a unique reason for their existence. Bill Gates got the idea for Microsoft when a friend, Paul Allen, showed him the 1975 issue of Popular Electronics. This issue featured a story on the Altair 8800, one of the first microcomputers. Both Gates and Allen saw the potential for developing a programming language for the computer. The rest is history.
In putting together the story behind your business, it may be necessary to hire a good writer to help craft the message. This expense is well worth it. Once you create a great story that customers can relate to, your next job is to write professional content for your site. Though your budget may initially be small, you can go back and redo this once you have the time and money to do so. Usually, your website is a work in progress anyway. It is constantly evolving and changing as your business grows.
Good content will make customers feel that they need your goods and services to meet a need in their lives. You should include information about how these goods or services are better than those offered by others in the same industry. Many of today’s most successful brands have become indispensable to their customers. Think about how Prada and Louis Vuitton have changed the handbag industry. Their customers feel a strong urge to own one of these exclusive handbags and many are priced in the thousands of dollars.
Provide contact information
Imagine locating a website online, and the information given therein is attractive. You read through their homepage and look at what they offer. In your mind, you are convinced that this is it. They are exactly what you need. You pick up your phone while scrolling down, looking for the contacts page and you find nothing. No phone number, no email address, no physical address, nothing. This can be frustrating, right? In fact, most people will get disheartened immediately. They will look elsewhere for other products. Consumers feel that if there’s no contact information, then the business must be shady. So it’s important to include your physical location, email address and phone number. This degree of transparency tells consumers that you have nothing to hide.
Collect lead information
If you have been in business long enough, you know that some clients will visit your website more than once before they decide to make a purchase. Do not despair, this does not mean that your website is not fulfilling its mandate. It simply means that many customers enjoy doing lots of shopping before they make a purchase. In the meantime, you can collect lead information using a lead capture form. This will give you the opportunity to send them regular email updates about specials you might have. Often, if a prospective customer gets an email telling them they can get a discount by making a purchase right away, they will do so. Everyone loves to get a great deal.
User friendly navigation
The whole science behind user experience has grown tremendously in the past ten years. A site must be intuitive, responsive and user friendly. You cannot afford to hide things or make it hard for customers to get information. Websites that are difficult to understand end up chasing customers away. Try to get regular feedback from users. Find out what people are saying about your site. Correct any issues you may have right away.
Final thoughts
All in all, if you maximize each component of your website, you will discover the magic that a great site can offer. Customers will visit often. They will tell their friends about your site. An amazing website truly is priceless.
This is the final of a three-part series about using Microsoft Excel 2016. It will cover some of the more advanced topics. If you aren’t great with numbers, don’t worry. Excel does the work for you. With the 2016 version of Excel, Microsoft really upped its game. Excel’s easy one-click access can be customized to provide the functionality you need.
If you haven’t read Part I and Part II of this series, it’s suggested that you do so. The webinar versions can also be found on our site or on YouTube.
This session will discuss the following:
More with Functions and Formulas
Naming Cells and Cell Ranges
Statistical Functions
Lookup and Reference Functions
Text Functions
Documenting and Auditing
Commenting
Auditing Features
Protection
Using Templates
Built-In Templates
Creating and Managing Templates
More With Functions And Formulas
Naming Cells And Cell Ranges
How do you name a cell? You do so by the cell’s coordinates, such as A2 or B3, etc. When you write formulas using Excel’s coordinates and ranges you are “speaking” Excel’s language. However, this can be cumbersome. For example, here G12 is significant because it refers to our Team Sales.
You can teach Excel to speak your language by naming the G12 cell Team Sales. This will have more meaning to you and your teammates. The benefits of naming cells in this fashion are that they are easier to remember, reduce the likelihood of errors, and use absolute references (by default).
To name our G12 cell Team Sales, right-click on the cell, choose Define Name, and type “Team Sales” into the dialog box. You can also add any comments you want here. Then click Ok.
Another way to do this is to click on the G12 cell and go up to the Name Box next to the Formula Bar, then type your name there.
And, there’s a third option at the top of the page called “Define Cells” that you can use.
Notice that there’s an underscore between Team and Sales (Team_Sales). There are some rules around naming cells:
You’re capped at 255 characters.
The names must start with a letter, underscore or a backslash ().
You can only use letters, numbers, underscores or periods.
Strings that are the same as a cell reference, for example B1, or have any of the following single letters (C,c,R,r) cannot be used as names.
How To Name A Range
Highlight an entire range of cells and name your range (we’re doing this in the upper left-hand corner).
Then you can easily use the name to produce the sum you need:
You won’t have to go back and forth from spreadsheet to spreadsheet clicking on specific cells to calculate your formula. You simply key in the name of the cell range you want to add. Just be sure to remember the names as you build your spreadsheets over time.
If you ever make a mistake or want to change names, you can go to Name Manager to do this.
Remember that if you move the cells, the name goes with it.
Statistical Functions
The three statistical functions are:
Average If
Count If
Sum If
The Average If can be used to figure out the average of a range based on certain criteria. Here we’re going calculate the Average If of the ERA of 20+ Game Winners from the spreadsheet we developed in our last session.
We’ve already named some of our cell ranges (wins, era). And we want to know the average greater than 19.
Hit Enter and you have the average.
You can use this feature across a wide variety of scenarios. For example, if you wanted to know the average sales of orders above a certain quantity – or units sold by a particular region, or the average profit by a distinct quarter.
Count If is used for finding answers to questions like, “How many orders did client x place?” “How many sales reps had sales of $1,000 or more this week?” or “How many times have the pitchers of the Philadelphia Phillies won the Cy Young Award?”
As you can imagine, it’s essential that you type in the text exactly the way you named that particular cell.
Hit Enter and you get your answer
Now we’re going to use the Sum If function to calculate the number of strikeouts by the pitchers on this list who are in the Baseball Hall of Fame.
Sum If is a good way to perform a number of real-world statistical analyses. For example, total commissions on sales above a certain price, or total bonuses due to reps who met a target goal, or total earnings in a particular quarter year-over-year.
Lookup and Reference Functions
These are designed to ease the finding and referencing of data, especially in large tables. Here, cells A1 and E3 relate to a variable interest rate that is paid on a bank account. For balances under $1,000, the interest rate is 3% – between $1,000 and $10,000, the interest rate is 4%, etc.
Cell A6 shows the balance of a specific account. The Lookup Function is used in B6. It looks up the interest rate and applies it to the account balance of $45,000. This is what the formula looks like in the bar at the top:
The vector form of the Excel Lookup Function can be used with any two arrays of data that have one-to-one matching values. For example, two columns of data, two rows of data, or even a column and a row would work, as long as the Lookup Vector is ordered (alphabetically or numerically), and the two data sets are the same length.
V Lookup and H Lookup are used to pull information into reports. We’re going to use Report Setup. Here, we have a worksheet that references salespeople, sales data, pricing, revenue, and the clients that they sold to. You’ll see on the top right where we set up a report with names referencing sales data.
You can access the sales reps in the drop-down menu. Pick a rep and use the V Lookup Function to find the price.
To Find Price, key in =vlookup and the corresponding cell number for Rep 16, plus the table array which is the entire table not including the header at the top.
Then you need the column index number. This is the number of columns to the right of your lookup value column, which is column A. It’s the 4th column from column A (Price).
Enter 4,
For range lookup we’re using true or false. We are entering false here.
Hit Enter and this is what you have for your Find Price value.
Now we’ll do a similar V Lookup for the Client. Copy and Paste:
Make the necessary changes in your formula:
Client 16 goes with Rep 16.
Note: If you change the Sales Rep, all the corresponding values will change.
If you have a lot of data and long tables, V Lookup helps you find information easily. The V stands for Vertical (or by column), because columns are vertical. H Lookup is for Horizontal-like column headers.
Text Functions
Text Functions contain some very powerful tools to adjust, rearrange and even combine data. These functions are used for worksheets that contain information and function as a database such as mailing lists, product catalogs, or even Cy Young Award Winners.
The first text function we’ll show you is concatenate. It links things together in a chain or series. Here, we have our Cy Young list. But we no longer need to see our Wins and Losses in a separate column.
To do this easily rather than manually, create a new column where your data will reside.
Hit Enter
Now, just go in and hide the Wins and Losses columns. Don’t delete them or your new column will have a reference error.
If you do want to delete the Wins and Losses columns, you must first make a new column. Copy the W-L numbers and Paste Value in the new column. This way you’ve moved from a formula to the new information. If you delete your source information without taking this step you’ll be left with nothing.
Combine as many columns as you need with the concatenate function to make the data appear as you need it to.
The Left Mid and Right Functions
These are used to tell Excel that you only want part of a text string in a particular cell. Here, we have a product list and product IDs that tell us the date of manufacturer, the item number, and the factory where it was made. We’re going to pull the data out so we can put it in columns to use in different ways.
We use the Mid Function here.
This works because each of the product IDs are the same length. If they were different lengths you’d have to do something more creative.
Documenting and Auditing
You want to make your Excel files easy to understand for both yourself and others who need to use them – and this includes auditors. An organized worksheet results in clear error-free data and functions.
Commenting
The purpose of commenting is to provide notes to yourself or especially to others. Comments can include reminders, explanations or suggestions.
You’ll find the New Comment button at the top under the Review Menu. Simply click the cell where you want the comment to go and click New Comment. Then type your comment and click outside the box to close it. The comment will disappear but it’s still there. Anywhere you see a red flag, there’s a comment.
If your name doesn’t appear in the comment, go to File>Options>General and personalize your copy of Excel (in this case Microsoft Office) under the User Name. You won’t need to go back and change each comment; Excel will do this for you.
To format a comment, click inside the comment box and a drop down will come up where you can format the text.
You can change the color of the box and lines around the box. Some managers have different colors for members of their teams.
If you change the default color, it will change that for all your Microsoft products.
To delete a comment, go to the cell that hosts it, then go up and hit delete.
If you have a lot of comments, grab the handle on the box and resize it.
Auditing
What we really mean is formula auditing. This is an advanced way to check your work.
The yellow diamond on the left of this cell indicates that there’s an error.
Or to find any errors, go to Formula Auditing in the top menu.
You have a number of helpful tools here. Trace Precedents shows where the formula looks for information. Click the formula you want and click Trace Precedents. It will display where your data came from.
Here’s a more complex formula and trace auditing:
To hide the arrows, click “Remove Arrows.”
Show Formulas
This expands all of your columns and shows all of them in a bigger way. You can go in and check your formulas on the fly very easily. Click Show Formulas again and the worksheet goes back to the way it was before.
Error Checking
This feature lets you check all formulas at once.
This makes it easy to find errors and correct them.
Evaluate Formula
This feature allows you to check a formula step-by-step. It shows the results of each individual part. It’s another great way to de-bug a formula that isn’t working for you. Click the formula you want to evaluate. Click Evaluate Formula and you’ll get a dialog box.
Click Evaluate and it will change the formula to the actual value that you can review. Each time you click Evaluate, it will take you through the steps of how you got to the final formula. You can trace your way through to see if you made any errors.
Protection
With protection you can lock in your changes in individual cells, spreadsheets, and entire workbooks. You can also protect comments from being moved or edited.
This is how to protect an entire workbook. It’s the highest level of protection.
You’ll want to do this if your workbook contains confidential information like:
Pre-released quarterly results
Employee salary tables
Staff member evaluations
Click File>Info>Protect Workbook>Encrypt with Password.
Enter your password and be sure to make note of it because it can’t be recovered if you lose it. You can use password management software to keep track of your passwords.
Once complete, click OK and your Protect Workbook function turns yellow indicating that you’ve protected your workbook.
To take off protection, retrace your steps.
You can also protect a current sheet you’re working on. It will take you back to your worksheet where you’ll be presented with a variety of options.
You can also protect cells and comments from this option.
In the same way you protected the worksheet, you can protect your workbook.
Using Templates
To see the variety of templates you can use in Excel, click File>New and you’ll be presented with a collection of 25 templates you can choose from.
For example, there’s a great Loan Amortization Schedule you can use. Formulas are built in for you. All you need to do is change the numbers.
You can also go online while inside Excel to find more. You don’t want to download templates from outside Excel because they may contain macros that are contaminated with viruses.
On the right side of the page, you have a huge selection to choose from.
It even provides employee time sheets you can use that can save you so much time trying to figure out formulas.
Creating and Managing Templates
Go to File>Info>Save As and save the template to your location, then save as an Excel Template.
Before you save as a template you want to:
Finalize the look and feel of your template
Use review and auditing tools
Remove unnecessary data and information
Unprotect cells and sheets as appropriate
Create comments as guides
Congratulations! Now you’re an Excel Pro! This completes our Excel Like a Pro Series. If you have any questions or need assistance, feel free to contact our Excel 2016 experts.
At nearly $1 Trillion in earnings a year, hacking is now at record proportions. Your data is a valuable asset, not only to you but to criminals as well. Don’t get hacked.
Here’s what you need to know.
1 in 3 Americans has been hacked.
A hacker attacks someone every 39 seconds.
61% of small businesses experienced a cyber-attack within the past year.
The average cost of a data breach in the U.S. is $7.35 Million.
$5 Billion was lost due to hacking in 2017. This is more than 15 times the total losses in 2016. Most of this cost was due to data breach fines, downtime, and productivity losses.
54% of breaches are caused by negligent employees who click on suspicious websites and emails.
20% of businesses experienced downtime of over 100 hours due to ransomware attacks.
64% of businesses paid ransoms even though paying doesn’t guarantee that data will be returned.
The anticipated cost of cybercrime in the next 3 years is$6 Trillion.
The pool of cybersecurity experts is shrinking. By 2021 there will be 3.5 Millionjobs that can’t be filled. The demand for security experts is increasing and is outpacing the supply.
5 THINGS TO DO RIGHT NOW
Ignore Ransomware Threat Popups and Don’t Fall for Phishing Attacks.
These attacks say that your data will be encrypted so you can’t access it, but in many cases, this isn’t true; it’s just a ploy to get you to click on something harmful. Once you click on the link, then you’re in trouble. You may have to pay a ransom to get your files unlocked.
Ransomware is a type of malicious software (malware) that blocks access to a computer. It infects, locks, or takes control of a system and demands a ransom to unlock it. It’s also referred to as a crypto-virus, crypto-Trojan or crypto-worm. It then threatens that your data will be gone forever if you don’t pay using a form of anonymous online currency such as Bitcoin.
Phishing is when a scammer uses fraudulent emails, texts, or copycat websites to get you to click a link so they can steal your confidential information. Thieves are looking for information like social security numbers, account numbers, login IDs, and passwords. They use this information to rob you of your money and your identity. The odds are good that phishing will work. A campaign of 10 messages has a better than 90% chance of getting clicked on. The majority of account takeovers come from simple phishing attacks where you or someone in your organization gets tricked into releasing private credentials and information.
Use Hard-to-Guess Passwords and Two-Factor Authentication.
Use complex passwords with 9+ characters and don’t reuse passwords across your different accounts. Consider using a password manager like LastPass. For accounts that support this, two-factor authentication is an extra step worth taking to ensure the privacy of your data. It requires both your password and an additional piece of information to log in to an account. The second piece could be a code the company sends to your phone or a random number generated by an application or token. Two-factor authentication will protect your account even if your password is compromised.
Secure Wi-Fi With a Virtual Private Network (VPN).
Hackers now emulate free open Wi-Fi to steal your IDs and passwords. You can be fooled when you try to login to free Wi-Fi in airports, restaurants, and other public areas. When this happens, everything that you type is copied and archived by these criminals and used against you. Using a VPN encrypts your Internet connection and protects your privacy. When you connect to the Wi-Fi over your Virtual Private Network, no one can see the information you send, and your privacy is safeguarded at all times.
Back Up Your Data.
Store data both onsite and offsite in a secure Enterprise-Based Cloud System. Back up your files regularly to ensure you have a duplicate of all your files and applications if your network is compromised. Traditional data backups can’t always restore all of an IT system’s data and settings. This is why you need both an onsite backup and a reliable backup via the Cloud. An enterprise-based cloud backup solution safeguards your data and ensures that it’s recoverable under any circumstance.
Hire a Reputable Technology Solutions Provider to Help.
A reputable Technology Solutions Provider can deploy a layered security protocol with regular software patches, vulnerability management, and continuously-updated endpoint protection. They can also provide Security Awareness Training for your employees to help them recognize potential threats. With the right provider, you’ll boost your defense posture and decrease the likelihood that a data breach will take down your business.
Don’t get hacked. Contact us, and we’ll keep your data secure.
