#how to sum values from table column and update when remove or add new row in jquery

[login360seo]

Data Science with SQL: Managing and Querying Databases

Data science is about extracting insights from vast amounts of data, and one of the most critical steps in this process is managing and querying databases. Structured Query Language (SQL) is the standard language used to communicate with relational databases, making it essential for data scientists and analysts. Whether you're pulling data for analysis, building reports, or integrating data from multiple sources, SQL is the go-to tool for efficiently managing and querying large datasets.

This blog post will guide you through the importance of SQL in data science, common use cases, and how to effectively use SQL for managing and querying databases.

Why SQL is Essential for Data Science

Data scientists often work with structured data stored in relational databases like MySQL, PostgreSQL, or SQLite. SQL is crucial because it allows them to retrieve and manipulate this data without needing to work directly with raw files. Here are some key reasons why SQL is a fundamental tool for data scientists:

Efficient Data Retrieval: SQL allows you to quickly retrieve specific data points or entire datasets from large databases using queries.

Data Management: SQL supports the creation, deletion, and updating of databases and tables, allowing you to maintain data integrity.

Scalability: SQL works with databases of any size, from small-scale personal projects to enterprise-level applications.

Interoperability: SQL integrates easily with other tools and programming languages, such as Python and R, which makes it easier to perform further analysis on the retrieved data.

SQL provides a flexible yet structured way to manage and manipulate data, making it indispensable in a data science workflow.

Key SQL Concepts for Data Science

1. Databases and Tables

A relational database stores data in tables, which are structured in rows and columns. Each table represents a different entity, such as customers, orders, or products. Understanding the structure of relational databases is essential for writing efficient queries and working with large datasets.

Table: An array of data with columns and rows arranged.

Column: A specific field of the table, like “Customer Name” or “Order Date.”

Row: A single record in the table, representing a specific entity, such as a customer’s details or a product’s information.

By structuring data in tables, SQL allows you to maintain relationships between different data points and query them efficiently.

2. SQL Queries

The commands used to communicate with a database are called SQL queries. Data can be selected, inserted, updated, and deleted using queries. In data science, the most commonly used SQL commands include:

SELECT: Retrieves data from a database.

INSERT: Adds new data to a table.

UPDATE: Modifies existing data in a table.

DELETE: Removes data from a table.

Each of these commands can be combined with various clauses (like WHERE, JOIN, and GROUP BY) to refine the results, filter data, and even combine data from multiple tables.

3. Joins

A SQL join allows you to combine data from two or more tables based on a related column. This is crucial in data science when you have data spread across multiple tables and need to combine them to get a complete dataset.

Returns rows from both tables where the values match through an inner join.

All rows from the left table and the matching rows from the right table are returned via a left-join. If no match is found, the result is NULL.

Like a left join, a right join returns every row from the right table.

FULL JOIN: Returns rows in cases where both tables contain a match.

Because joins make it possible to combine and evaluate data from several sources, they are crucial when working with relational databases.

4. Aggregations and Grouping

Aggregation functions like COUNT, SUM, AVG, MIN, and MAX are useful for summarizing data. SQL allows you to aggregate data, which is particularly useful for generating reports and identifying trends.

COUNT: Returns the number of rows that match a specific condition.

SUM: Determines a numeric column's total value.

AVG: Provides a numeric column's average value.

MIN/MAX: Determines a column's minimum or maximum value.

You can apply aggregate functions to each group of rows that have the same values in designated columns by using GROUP BY. This is helpful for further in-depth analysis and category-based data breakdown.

5. Filtering Data with WHERE

The WHERE clause is used to filter data based on specific conditions. This is critical in data science because it allows you to extract only the relevant data from a database.

Managing Databases in Data Science

Managing databases means keeping data organized, up-to-date, and accurate. Good database management helps ensure that data is easy to access and analyze. Here are some key tasks when managing databases:

1. Creating and Changing Tables

Sometimes you’ll need to create new tables or change existing ones. SQL’s CREATE and ALTER commands let you define or modify tables.

CREATE TABLE: Sets up a new table with specific columns and data types.

ALTER TABLE: Changes an existing table, allowing you to add or remove columns.

For instance, if you’re working on a new project and need to store customer emails, you might create a new table to store that information.

2. Ensuring Data Integrity

Maintaining data integrity means ensuring that the data is accurate and reliable. SQL provides ways to enforce rules that keep your data consistent.

Primary Keys: A unique identifier for each row, ensuring that no duplicate records exist.

Foreign Keys: Links between tables that keep related data connected.

Constraints: Rules like NOT NULL or UNIQUE to make sure the data meets certain conditions before it’s added to the database.

Keeping your data clean and correct is essential for accurate analysis.

3. Indexing for Faster Performance

As databases grow, queries can take longer to run. Indexing can speed up this process by creating a shortcut for the database to find data quickly.

CREATE INDEX: Builds an index on a column to make queries faster.

DROP INDEX: Removes an index when it’s no longer needed.

By adding indexes to frequently searched columns, you can speed up your queries, which is especially helpful when working with large datasets.

Querying Databases for Data Science

Writing efficient SQL queries is key to good data science. Whether you're pulling data for analysis, combining data from different sources, or summarizing results, well-written queries help you get the right data quickly.

1. Optimizing Queries

Efficient queries make sure you’re not wasting time or computer resources. Here are a few tips:

*Use SELECT Columns Instead of SELECT : Select only the columns you need, not the entire table, to speed up queries.

Filter Early: Apply WHERE clauses early to reduce the number of rows processed.

Limit Results: Use LIMIT to restrict the number of rows returned when you only need a sample of the data.

Use Indexes: Make sure frequently queried columns are indexed for faster searches.

Following these practices ensures that your queries run faster, even when working with large databases.

2. Using Subqueries and CTEs

Subqueries and Common Table Expressions (CTEs) are helpful when you need to break complex queries into simpler parts.

Subqueries: Smaller queries within a larger query to filter or aggregate data.

CTEs: Temporary result sets that you can reference within a main query, making it easier to read and understand.

These tools help organize your SQL code and make it easier to manage, especially for more complicated tasks.

Connecting SQL to Other Data Science Tools

SQL is often used alongside other tools for deeper analysis. Many programming languages and data tools, like Python and R, work well with SQL databases, making it easy to pull data and then analyze it.

Python and SQL: Libraries like pandas and SQLAlchemy let Python users work directly with SQL databases and further analyze the data.

R and SQL: R connects to SQL databases using packages like DBI and RMySQL, allowing users to work with large datasets stored in databases.

By using SQL with these tools, you can handle and analyze data more effectively, combining the power of SQL with advanced data analysis techniques.

Conclusion

If you work with data, you need to know SQL. It allows you to manage, query, and analyze large datasets easily and efficiently. Whether you're combining data, filtering results, or generating summaries, SQL provides the tools you need to get the job done. By learning SQL, you’ll improve your ability to work with structured data and make smarter, data-driven decisions in your projects.

[tableauecoursetips]

How to Create a Tableau Prep Flow: A Step-by-Step Guide for Beginners

When working with raw, unstructured data, cleaning and preparing it is one of the most crucial steps in the analytics process. That’s exactly where Tableau Prep shines. This powerful tool helps users visually shape, clean, and combine data—without writing complex code or scripts.

In this guide, you’ll learn how to create a Tableau Prep flow step by step, perfect for both beginners and professionals aiming to streamline their data preparation process.

What Is Tableau Prep?

Tableau Prep is a dedicated data preparation tool within the Tableau ecosystem. It allows you to connect to multiple data sources, clean and transform your data, merge tables, and output structured datasets ready for visualization in Tableau Desktop.

Key Components of Tableau Prep

Input: Load raw data from various sources.

Cleaning Steps: Modify and fix data issues visually.

Joins/Unions: Combine data from multiple tables or files.

Output: Save the cleaned data for use in Tableau dashboards.

Step-by-Step: How to Create a Tableau Prep Flow

Step 1: Open Tableau Prep Builder

Launch Tableau Prep Builder and start a new flow or open an existing one. The intuitive interface makes it easy to get started right away.

Step 2: Connect to Your Data

Click Connect to Data and select your source. Tableau Prep supports:

Excel files

CSV files

SQL databases

Google Sheets, and more

Choose the relevant tables or sheets to include in your flow.

Step 3: Add and Configure Input Steps

Drag your selected data into the workspace, creating an Input Step. Here you can:

Rename columns for clarity

Remove unnecessary fields

Apply filters to limit data size

If you’re pursuing the Best Tableau classes in Chandigarh, you’ll find hands-on sessions greatly simplify mastering this step.

Step 4: Clean Your Data

Add a Clean Step by clicking the plus (+) icon next to your Input Step. Tableau Prep offers tools to:

Split fields for better structure

Remove null or invalid values

Change data types to match your needs

Group and replace similar values to fix inconsistencies

Visual data profiles help you quickly spot and resolve data quality issues.

Step 5: Combine Data with Joins or Unions

Need to work with multiple tables? Use these steps:

Join Step: Match records from two datasets using common fields.

Union Step: Stack rows from different tables vertically.

Tableau Prep shows visual feedback, letting you verify joins or unions instantly.

Step 6: Aggregate Your Data (Optional)

If summarizing data is required—like total sales by region—add an Aggregate Step. Select fields to group by and choose aggregation types such as sum, average, or count.

Step 7: Output Your Data

Once your flow is ready, click Add Output. You can export your cleaned data as:

Tableau Data Extract (.hyper)

CSV file

Publish directly to Tableau Server or Tableau Cloud

Name your output file, select a destination, and run the flow to generate your cleaned dataset.

Best Practices for Tableau Prep

Always preview your output to ensure accuracy.

Use descriptive names for each step to keep your workflow organized.

Document your process for easy collaboration and future updates.

Save flows as templates to reuse for repetitive tasks.

Conclusion

Creating a Tableau Prep flow is a straightforward yet powerful way to transform raw data into clean, structured information ready for analysis. Thanks to its visual, drag-and-drop interface, Tableau Prep makes data cleaning accessible—even for those without technical backgrounds.

Whether you’re self-learning or enrolled in the Best Tableau classes in Chandigarh, mastering Tableau Prep is an essential skill for any aspiring data professional. It not only saves time but also ensures your analysis is built on high-quality, reliable data.

Start experimenting with Tableau Prep today and take your data visualization journey to the next level!

[codehunger]

How to dynamically add/remove table row in jquery with example

How to dynamically add/remove table row in jquery with example

In this article, we will learn about how we can add/remove table rows in jquery as well as we will remove the selected row. Believe me, removing table rows in jquery going to be so easy simple you will understand easily how to remove/add table rows in jquery. To achieve the add/remove table row in jquery, I have used two predefined jquery functions one is append() and the second is…

[weekendbanana]

Function insert current date in excel

#Function insert current date in excel how to

#Function insert current date in excel series

These are volatile functions, which means any change in the Excel workbook will cause them to recalculate. FunctionsĮxcel has two functions that will give us the date and time. This shortcut also works while in edit mode and will allow us to insert a hardcoded date into our formulas. Pressing Ctrl + Shift + will enter the current time into the active cell Pressing Ctrl + will enter the current date into the active cell. The dates and times created will be current when they are entered, but they are static and won’t update. These are both quick and easy ways to enter the current date or time into our Excel workbooks. Video TutorialĮxcel has two great keyboard shortcuts we can use to get either the date or time. In this post, we’re going to look at 5 ways to get either the current date or current time into our workbook. The great news is there a lot of ways to get this information into Excel. Excel displays a context menu and selects the appropriate option.The current date and time is a very common piece of data needed in a lot of Excel solutions. Another way to enter your first date will be to right-click the fill handle and drag and release the fill handle through the cells you automatically want to fill with dates.Click the AutoFill Options icon and choose the option you want when the range is populated by sequential dates. You can use the above-mentioned Excel AutoFill options.There are two ways of automatically adding weekdays, months, or years to the selected range of cells. Click the first date on your cell and then drag the fill handle to or from the cells you want Excel to add dates.Enter the original date in the first cell.It is a common way to automatically fill a column or row.

#Function insert current date in excel series

To autofill a series of dates in which one day is incremented, you can use the Excel AutoFill function.

The functions take the current system clock date and time.

The date and the time returned will not be refreshed on an ongoing basis, but only when the chain is reopened or re-calculated.

Please remember that when using the Excel date functions:

Excel Dynamic Chart Linked with a Drop-down List.

#Function insert current date in excel how to

How to calculate Sum and Average of numbers using formulas in MS Excel?.How to Apply Conditional Formatting Based On VLookup in Excel?.How to Find the Slope of a Line on an Excel Graph?.COUNTIF Function in Excel with Examples.Stacked Column Chart with Stacked Trendlines in Excel.How to Calculate Euclidean Distance in Excel?.How to Format Chart Axis to Percentage in Excel?.How to Calculate Mean Absolute Percentage Error in Excel?.How to Calculate Root Mean Square Error in Excel?.Statistical Functions in Excel With Examples.How to Create Pie of Pie Chart in Excel?.How to Calculate the Interquartile Range in Excel?.How to Enable and Disable Macros in Excel?.Positive and Negative Trend Arrows in Excel.Plot Multiple Data Sets on the Same Chart in Excel.How to Find Correlation Coefficient in Excel?.How to Automatically Insert Date and Timestamp in Excel?.How to Remove Pivot Table But Keep Data in Excel?.How to Find Duplicate Values in Excel Using VLOOKUP?.How to Show Percentage in Pie Chart in Excel?.Highlight Rows Based on a Cell Value in Excel.How to Remove Time from Date/Timestamp in Excel?.ISRO CS Syllabus for Scientist/Engineer Exam.ISRO CS Original Papers and Official Keys.GATE CS Original Papers and Official Keys.

[photoload409]

Manual Pivot Table On Mac Excel 20008

Pivot Table In Mac Excel

Pivot Table On Mac Excel 2016

Manual Pivot Table On Mac Excel 2008 Tutorials

What is a running total?

Excel 2008 for mac pivot tables excel 2008 for mac pivot tables excel for mac introducing pivottable ms excel 2017 for mac how to create a. By Geetesh Bajaj, James Gordon. In Excel 2011 for mac, a PivotTable is a special kind of table that summarizes data from a table, data range, or database external to the workbook.If you’re PivotTable aficionado, you will be in seventh heaven with the new PivotTable capabilities in Office 2011 for Mac. Manual Pivot Table On Mac Excel 2008 Shortcut Keys If you select a row or column label in the pivot table, then click the Sort button on the ribbon, you’ll see that sort options are set to Manual. To return a pivot table to it’s original sort order at any time, just sort the field alphabetically again. Pivot Charts Are Here!

A running total in Excel (also known as cumulative sum) refers to the partial sum of a data set. It is a summation of a sequence of numbers that is refreshed every time a new number is added to the sequence.

Running totals are very commonly used in Excel, especially when daily data is involved such as daily sales, daily bank balance, daily calorie intake or the scores of a sports game. It reveals the total number for each day or month, depending on the measurements.

How to create a running total

There are many ways to create a running total, including using simple addition, the SUM function, and Pivot Tables.

The combination of Photos (for iOS) and Photos (for the Mac) also work together in some spectacular ways—like iCloud Photo Library. This feature, should you choose to accept it, stores all of your photos and videos online—and lets you view them on any Apple product (Mac, iPhone, iPad, etc.) identically. ‎Apple’s new Photos app lets you do a whole lot more than simply store and edit pictures and videos on a Mac or iOS device. With this comprehensive guide, you’ll learn how to import, organize, and share your digital memories, as well as how to improve, print, and use your photos in creative projects. https://photoload409.tumblr.com/post/652943496337129472/photos-for-mac-and-ios-the-missing-manual.

Let’s look at how to create a basic running total by using addition to familiarize you with the logic behind it.

Download your free running total practice file

Cheap mac cosmetics mac makeup wholesale cheap mac makeup remover. Use this free Excel file to practice along with the tutorial.

Below is a credit card statement with various expenses and credits in the list. A running total helps keep track of the credit limit available and personal expenditures.To create a running total, click D2 and enter =C2, the beginning credit limit to start with.

Given that running totals reveal the summation of the data as new items are added to the total mix, to keep the changes:

Click Cell D3, enter =D2+C3.

This is to add the beginning credit limit and the new item — an expense from Whole Foods.

Now Cell D3 indicates the credit limits after deducting the expense from Whole Foods — $4916.

Pivot Table In Mac Excel

To find out the remaining credit limits available, drag down the formula in D3 and apply it to the rest of the cells under column D.

From the formulas, you can tell that each value of the running total takes reference from the previous value of running total and adds on the value of the new item.

Below is the full picture, with running total detailing the movement of the credit limit with each item added on.

How to calculate a running total in Excel

As mentioned previously, there are multiple ways to calculate running totals in Excel, depending on the complexity of the situation on hand and the usage.

1. Create running total by using the SUM function

Creating a running total by using the SUM function is pretty similar to using simple addition.

In Cell D2, enter:

This is to add up the value of the beginning credit limit and the header — Running Total. The header contains no value itself, and will be considered as 0 in the calculation.

This, unlike addition, saves you from the extra work of creating the beginning balance first, then adding the new item in the second cell.

Copy the formula in the rest of the cell. It should look like the image below, with each running total taking reference from the previous values.

Though, when a row is added, there will be a gap in the running total, and users will need to copy the formula and drag it down to refresh the rest.

Likewise, when a row is deleted, error #REF! shows as the cell reference is removed. To calculate the running total, copy the formula from the last correct cell (D5) and apply to the rest.

The SUM function makes it quick and easy to calculate the running total. However, when adjustments to the data are required (e.g. adding or deleting a row) users will need to manually adjust and re-apply the correct formulas to the cells.

If the data set is small with a few calculations or sheets involved, manual adjustments are straightforward. However, if the data set is large with multiple sheets and cell referencings involved, manual adjustments will be more difficult and might lead to errors.

2. Create running total by using the SUM function and mixed reference

Users can include mixed reference — both absolute and relative reference — in the SUM formula to calculate the running total.

In Cell D2, enter:

This is to lock the reference to cell C2, so the summation will always begin from cell C2.

Copy the formula and drag it down to apply it to the rest of the cells.

As you can see below, the running total in cell D5 calculates the summation of the values from C2 to C5.

The summation will always begin from cell C2 as it has been locked with the $ sign (absolute reference), and includes any other values between C2 and C5 (a relative reference).

Same as the SUM Function, inserting and removing a row will create errors in the formulas and will require manual adjustments.

3. Create running total by using Pivot Tables

Pivot Tables are a powerful feature in Excel that allow you to organize, summarize, and analyze tables. It’s commonly used to sort, group, calculate the sum, average or count the values. Damage psychology. A Pivot Table can calculate running totals as well.

In a new spreadsheet, create the Pivot Table by using the same set of data.

Under Rows, add Date and Description

Under Values, include movement twice. This is so that column C will be showing the daily subtotal and total of the movement. And column D — Sum of Movement 2 will be modified to show running total later.

For easy viewing, the Pivot Table is shown using the outline form below.

In the Pivot Table Fields, right click Sum of Movement 2 to access the menu selection. Click Value Field Settings.

The Value Field Settings dialogue box will appear. Plenty of things can be done here.

First, change the name to “Running Total” to differentiate it.

Select Tab — Show Values As, and in the dropdown list, find Running Total In, so the values will be shown as Running Total. In the Base Field, select — Date, as the running total will be performed based on the dates. Click OK.

Now, the Pivot Table displays the running total of the credit movement by dates. The label of the field is updated as well to Running Total.

In this example, the subtotals are shown at the top of each group. You are free to change it to the bottom of each group if that suits your habits better.

Under the Design Tab, find Subtotals, then click — Show all Subtotals at Bottom of Group.

Now the Pivot Table displays all the information in an organized manner, detailing the activities by date and the totals of the credit movements and the running total by dates.

If there are any changes to the source data, whether to add or delete a row, simply click refresh, and the table will reflect the changes automatically.

There is no perfect way to create running totals in Excel. All three methods reveal the same results, and each has their own pros and cons. It depends on the complexity of the data set and the calculations on hand.

Differences on Windows and Mac

The steps required to perform running total on Windows and Mac are the same.

Summary

Running totals in Excel (also known as cumulative sum) are useful to keep track of progression and changes over time, especially when there is new data coming in or old data being removed from the data set. It’s usually used to monitor sales patterns, bank balance, calorie intake, utility charges and scores of sport games.

There are multiple ways to create running totals in Excel, each with their own pros and cons. It’s important to consider one’s needs and review the data set before choosing the most efficient method to calculate the running total in Excel.

Try the GoSkills Microsoft Excel - Basic and Advanced course today to improve your skills in Excel.

Level up your Excel skills

Pivot Table On Mac Excel 2016

Become a certified Excel ninja with GoSkills bite-sized courses

Manual Pivot Table On Mac Excel 2008 Tutorials

Start free trial

[trendingjobs]

Sql Interview Questions You'll Bear in mind

The course has plenty of interactive SQL practice exercises that go from easier to testing. The interactive code editor, data collections, and also difficulties will certainly assist you cement your expertise. Nearly all SQL job prospects undergo specifically the same stressful procedure. Right here at LearnSQL.com, we have the lowdown on all the SQL method as well as prep work you'll need to ace those interview inquiries as well as take your job to the following degree. https://bit.ly/3tmWIsh is layer the aspects of progress of de facto mop up of test cases defined in the design and neuter the narrative % in signed up with requirementset. If you're talking to for pliant docket tasks, below are 10 interview concerns to ask. Make certain to shut at the end of the interview. And also how can there be impedimenta on liberation comey. The initial event to honor or two the emplacement is that individuals. We have to give the void problem in the where condition, where the entire data will replicate to the brand-new table. NOT NULL column in the base table that is not selected by the view. Partnership in the database can be specified as the connection between greater than one table. Between these, a table variable is quicker primarily as it is saved in memory, whereas a temporary table is kept on disk. Hibernate allow's us create object-oriented code as well as inside converts them to indigenous SQL questions to perform versus a relational data source. A database trigger is a program that instantly executes in feedback to some occasion on a table or view such as insert/update/delete of a document. Generally, the data source trigger aids us to keep the integrity of the data source. Additionally, IN Declaration runs within the ResultSet while EXISTS keyword operates digital tables. In https://tinyurl.com/c7k3vf9t , the IN Declaration also does not operate inquiries that connects with Virtual tables while the EXISTS search phrase is utilized on connected inquiries. The MINUS keyword basically deducts between two SELECT queries. The result is the distinction between the very first query and also the 2nd inquiry. In case the dimension of the table variable surpasses memory size, then both the tables perform likewise. Referential stability is a relational database idea that suggests that accuracy and consistency of information need to be kept between main and also international keys. Q. Checklist all the possible values that can be saved in a BOOLEAN data area. A table can have any variety of foreign keys defined. Accumulated question-- A question that sums up information from multiple table rows by utilizing an aggregate feature. Hop on over to the SQL Practice training course on LearnSQL.com. This is the hands-down best location to assess and also combine your SQL skills prior to a huge meeting. You do have full web access and if you need more time, feel free to ask for it. They are extra worried regarding the end product instead of anything else. However make no mistake regarding believing that it will be like any type of coding round. They do a via end to end look at your sensible in addition to coding capability. As well as from that you need to evaluate and execute your strategy. This will not call for front end or data source coding, console application will certainly do. So my review here need to get data and then conserve them in listings or something to make sure that you can utilize them. Item with the second interview, you will certainly find far and away often that a more elderly partner or theatre director mostly performs these. Buyers wish to make a move ahead their buying big businessman gets scoured. Get conversations off on the right track with discussion starters that ne'er pave the way. The last stages of a discover telephone call should be to steer far from articulating frustrations and also open up a discourse nigh completion result a result might pitch. Leading new house of york stylist zac posen dealt with delta workers to make the unique consistent solicitation which was introduced one twelvemonth ago. The briny affair youâ $ re stressful to figure out is what they understanding as well as what they do otherwise now. And also this is a rather complicated inquiry, to be truthful. However, by asking you to produce one, the questioners can inspect your command of the SQL syntax, in addition to the method which you approach addressing a problem. So, if you don't procure to the best response, you will most likely be offered time to think and also can definitely catch their focus by just how you attempt to address the trouble. Using a hands-on technique to managing realistic jobs is many times way more important. That's why you'll have to deal with practical SQL meeting questions, also. You can wrap up the two questions by saying there are 2 sorts of database administration systems-- relational and non-relational. SQL is a language, developed only for working with relational DBMSs.

It was developed by Oracle Corporation in the early '90s. It adds step-by-step attributes of programs languages in SQL. DBMS sorts out its tables through a hierarchal way or navigational fashion. This is useful when it pertains to keeping information in tables that are independent of one another as well as you don't desire to change other tables while a table is being loaded or edited. variety of online database courses to aid you end up being an specialist as well as break the meetings quickly. Join is a inquiry that fetches associated columns or rows. There are four types of joins-- internal sign up with left join, right join, as well as full/outer sign up with. DML permits end-users insert, update, obtain, and remove information in a data source. This is one of the most preferred SQL interview inquiries. A clustered index is used to buy the rows in a table. A table can possess only one gathered index. Restraints are the depiction of a column to implement information entity and uniformity. There are two levels of constraint-- column level and table level. Any type of row common across both the result set is removed from the final outcome. The UNION search phrase is utilized in SQL for integrating multiple SELECT inquiries however deletes replicates from the result collection. Denormalization enables the retrieval of areas from all typical kinds within a database. With respect to normalization, it does the contrary and puts redundancies into the table. SQL which represents Criterion Query Language is a web server programs language that supplies communication to data source fields and also columns. While MySQL is a type of Data source Monitoring System, not an real shows language, even more especially an RDMS or Relational Data Source Monitoring System. Nevertheless, MySQL also carries out the SQL phrase structure. I addressed every one of them as they were all straightforward concerns. They informed me they'll contact me if I get selected as well as I was rather positive due to the fact that for me there was nothing that failed but still I obtained nothing from their side. Standard questions about family, education and learning, tasks, placement. As well as a little conversation on the responses of sql as well as java programs that were given up the previous round. INTERSECT - returns all distinct rows chosen by both questions. The procedure of table layout to minimize the information redundancy is called normalization. We require to divide a database right into 2 or more table and also define connections between them. Yes, a table can have lots of international secrets and also just one primary key. Keys are a important feature in RDMS, they are essentially areas that link one table to another and also advertise rapid information retrieval and also logging through managing column indexes. In regards to data sources, a table is referred to as an arrangement of organized access. It is further divided into cells which contain various fields of the table row. SQL or Structured Query Language is a language which is made use of to interact with a relational data source. It supplies a means to adjust and also create databases. On the other hand, PL/SQL is a language of SQL which is used to enhance the capabilities of SQL. SQL is the language made use of to create, upgrade, and also change a data source-- articulated both as 'Se-quell' and also'S-Q-L'. Prior to starting with SQL, let us have a quick understanding of DBMS. In simple terms, it is software program that is used to create and take care of databases. We are mosting likely to stick with RDBMS in this article. There are likewise non-relational DBMS like MongoDB used for large information analysis. There are different profiles like data analyst, data source administrator, as well as information architect that need the knowledge of SQL. Besides leading you in your meetings, this article will likewise give a basic understanding of SQL. I can also recommend "TOP 30 SQL Meeting Coding Tasks" by Matthew Urban, actually wonderful publication when it involves one of the most common SQL coding meeting concerns. This mistake generally shows up due to syntax errors standing by a column name in Oracle data source, discover the ORA identifier in the mistake code. Make https://geekinterview.net key in the right column name. Likewise, take unique note on the pen names as they are the one being referenced in the error as the void identifier. Hibernate is Things Relational Mapping device in Java.

[readersforum]

How to Learn Excel Online: 21 Free and Paid Resources for Excel Training

New Post has been published on http://www.readersforum.tk/how-to-learn-excel-online-21-free-and-paid-resources-for-excel-training-2/

How to Learn Excel Online: 21 Free and Paid Resources for Excel Training

Like many marketers, I have a bit of experience with Microsoft Excel. I’ve used it to organize events, plan meals, and sort data — but I don’t have nearly the advanced knowledge I wish I did.

And thanks to those limited skills, I’m constantly subjecting myself to the tedium of updating my spreadsheets manually.

Like me, you’re missing out on a world of Excel training courses that could teach me how to automate my reports and save hours of time.

When I asked even my most Excel-savvy colleagues where they picked up their knowledge, they told me things like, “I mostly learned from colleagues and friends,” or, “When I have a specific question, I ask someone or search on Google.” Fair enough. But as a beginner, I probably have a few too many Excel questions to rely on colleagues — or Google — to answer every one.

I can’t be the only one out there who wants to master the world’s most popular data analysis and visualization solution — or at least learn how to create charts and graphs that’ll impress my manager.

So in the spirit of becoming a more productive, data-driven marketer, I scoured the internet for the best online resources for learning Excel. Half of them are free, and the ones that aren’t might be worth the investment. In these trainings, you’ll learn the following:

Learning Excel

Create PivotTables to find relationships between data.

Enter formulas across cells, rows, and columns.

Conduct a VLOOKUP across an entire column.

Run accounting functions to track business finances.

Group, ungroup, and reformat rows and columns.

Perform data validation to control the format of cell values.

Use keyboard shortcuts to make quick changes to cell values.

Create charts from your spreadsheet data.

Develop histograms of data from the charts you create.

Create dashboards to manage multiple groups of performance data.

Add conditional formatting to rows and columns that fit certain criteria.

Print your Excel spreadsheet for presenting to others.

Take a look at the free and paid resources below. Bookmark your favorites and get that much closer to working more efficiently in Excel.

How to Learn Excel With Free Training Resources

1. Microsoft’s Excel Training Center

When it comes to learning a new application, why not start at the source? After all, no one knows Excel better than the people at Microsoft.

In fact, they’ve done a great job putting together the Office Training Center: A resource hub for all Microsoft Office applications and services. The training center for Excel has a whole bunch of free tutorials, videos, and guides on Windows, Mac OS, Android, iOS, and Windows Phone that cover the latest version of Excel, as well as older ones.

Once you click into a platform, you’ll find resources divided by Excel ability: For beginners (like basic math and creating a chart), intermediate users (like sorting and filtering data, conditional formatting, and VLOOKUPs), and advanced users (like pivot tables, advanced IF functions, and how to password-protect worksheets and workbooks).

Source: Microsoft

2. Excel Exposure

Excel Exposure is a free series of written and video-based courses to help you learn Excel regardless of your skill level. There are nearly 40 lessons — split into “Beginner,” “Intermediate,” and “Advanced” — each no more than 20 minutes long.

In addition to this a-la-carte curriculum, Excel Exposure offers a “Master Workbook” that lives directly in Excel to help you practice what you learn in each lesson. Learnings include data and time functions, how to group and ungroup rows and columns, using conditional formatting, PivotTables, chart design, and more.

3. HubSpot Excel Resources

Seeing as Excel is one of the most in-demand skills for data-driven marketers — and because we want marketers like you to succeed — we’ve created some of our own educational content about Excel here at HubSpot. From free ebooks, to templates, to video tutorials, we aim to cover a wide range of Excel-relevant topics.

Here are a few of our best:

“How to Use Excel: The Essential Training Guide for Data-Driven Marketing” (free ebook)

“9 Excel Templates to Make Marketing Easier” (free templates)

“How to Create a Pivot Table in Excel: A Step-by-Step Tutorial” (video & blog post)

4. The Spreadsheet Page

Here’s a very well-organized site that’s chock full of helpful Excel tips, collected by an expert named John Walkenbach. Over the past 30 years, he’s written more than 60 Excel books for users of all levels, and around 300 articles and reviews for magazines like InfoWorld, PC World, and PC/Computing. At one point, he wrote the monthly spreadsheet column for PC World. In other words, the man knows his stuff — and he knows how to present it.

The most helpful part of his website is probably the Excel Tips tab, which has a long list of useful pointers on formatting, formulas, charts and graphics, and printing. The tips themselves include everything from working with fractions, to unlinking a pivot table from its source data, to spreadsheet protection FAQs.

The Downloads tab is another particularly helpful section of Walkenbach’s site, where he’s added free, ungated download links to files he created, like free Excel workbooks and add-ins. For example, there’s one Excel workbook available for download that gives examples of custom number formats, which you can play with and tweak on your own time, and get familiar with them without having to start from scratch.

5. About.com’s Spreadsheets Page

Many of you are likely familiar with the content website About.com, but did you know it has its own spreadsheets subdomain — much of which is devoted to Excel? There are likely thousands of instruction sets on that site, most of which are illustrated, how-to posts. Plus, fresh content is added regularly.

Each piece of content is categorized according to everything from formulas and formatting, to videos, tools, and templates. If you want to stay up-to-date on the latest spreadsheet news and tips, you can sign up for a free newsletter. There’s just one caveat: the site contains a good amount of ads — but if you can stand them, the content is worth it.

6. Contextures

Contextures is a place for free Excel instructions in the form of quick tips, blog posts, a newsletter, and even .zip files you can download to see how various functions work in the context of real tables of data.

This resource has literally hundreds of downloadable spreadsheets that open in Excel and offer step-by-step instructions for performing data validation, adding filters to rows and columns, adding conditional formatting to rows and columns, designing charts, creating PivotTables, running functions, and more.

7. Chandoo.org

Purna “Chandoo” Duggirala, Chandoo.org‘s founder, says he has one goal: “to make you awesome at Excel and charting.” He started the blog in 2007 and, today, it contains more than 450 articles and tutorials on using Excel and making better charts. He’s built the blog as a community, citing values like humility, passion, fun, and simplicity.

He also works to make it a valuable resource for the folks for whom English is not their first language.

Most of his tips stem from forums, where people ask questions about Excel — about formulas, formatting, shortcuts, pivot tables, and so on — and anyone can answer them. Chandoo then uses some of the more helpful forum questions to create articles and tutorials.

Source: Chandoo.org

But it’s not all so formal. For example, Chandoo once created a digital Easter egg hunt for a blog post, which included a downloadable Excel workbook containing seven hidden pandas. Readers were challenged to locate the pandas using clues, Excel techniques, and even “I-Spy” skills.

Source: Chandoo.org

While the articles, forums, and other parts of the site are free, you can pay to join one of Chandoo’s structured training programs, like Excel School ($97 – $247), or VBA Classes ($97 – $347). Plus, there’s aways the option to buy one of his books — The VLOOKUP Book or Excel Formula Helper Ebook.

8. Excel Easy

Excel Easy is a comprehensive tutorial for learning Excel, breaking down its learnings into chapters. Those chapters are sorted into the following sections:

Introduction

Basics

Functions

Data Analysis

VBA

Examples

In each chapter, Excel Easy walks you through what it perceives to be the fundamentals of Excel, in the form of written guides with screenshots to help you master each concept. At the end of the tutorial, you have access too 300 examples showing you how to perform each concept in real scenarios. Concepts include basic formulas and functions, how to create a ribbon, creating multiple worksheets, formatting cells, data validation, table creation, and more.

9. MrExcel.com

Here’s a resource that puts we mere mortals in touch with Excel experts. MrExcel.com‘s claim to fame is its interactive message board, which is constantly monitored by its community of Excel gurus.

The board is organized according to subject, like general announcements, questions, and MrExcel.com products. When a user posts a question, a member of the MrExcel.com expert community will reply with an answer. The questions range from simplifying an Excel task, to solving urgent inquiries.

Not a native English speaker? You can ask questions in your native language.

Aside from posting questions on the message board, you can also browse Mr. Excel’s “Hot Topics” — found on the left-hand side of its homepage — which includes things like finding the cumulative sum of even or odd rows, or removing the leading zero within a text field. The site also has a library of helpful Excel books and ebooks, and if you need help with problems that are more complex, you can even hire an Excel consultant directly from the website, for a fee.

10. Exceljet

Exceljet offers hundreds of free formula walk-throughs, videos, and blog posts that allow you to itemize your Excel training based on the project you’re working on. It also has several paid programs that take a deeper dive into specific Excel topics its users — of which there are more than 10,000 — are interested in.

11. EdX Excel Courses

Here’s a budget-friendly option for those in search of a more formal course, rather than a one-off tutorial: EdX is a nonprofit that provides free education for people around the globe — with an interesting model of Excel training sessions, both timed and self-paced.

When users enroll in a course that’s marked as “Verified,” they have the option to pay a fee in exchange for an instructor-signed certificate with the institution’s logo, to verify the achievement and increase job prospects. Those fees are used to fund the courses, giving you the option to take them for free if you don’t mind foregoing the certificate.

Otherwise, there are some courses offered at a “Professional Education” level, for which the fee isn’t optional. One example is the Business and Data Analysis Skills course, offered for $60.

To help you choose the right one, each edX course includes reviews (with a rating up to five stars), and information on length and amount of effort, usually measured in hours per week. There are also details on the level of knowledge required, along with video transcripts.

12. Annielytics Video Tutorials

Annie Cushing, a web analytics data expert, created the Annielytics blog and YouTube channel to share her knowledge with the world. Don’t let the punny name fool you — both are chock full of really good, specific, and in-depth web analytics tips.

While the content here isn’t all Excel-related — much of it is about Google Analytics, for example — it does contain some great Excel video tutorials. Even better, they were created with marketing and web analytics in mind, so they’re directly applicable to things like marketing data reports. The Excel-specific videos can be found here, or by searching her YouTube channel for “Excel”.

The Excel topics vary widely, from how to create interactive pivot tables, to how to add a scrolling table to your dashboard using the INDEX function. The videos also vary in length depending on topic complexity, ranging from two-and-a-half-minutes, to those over half an hour long. To give you an idea of what the videos are like, here’s one of our favorites, which covers a comprehensive overview of Excel charts:

13. Khan Academy

When visitors arrive at the Khan Academy website, they’re greeted with two simple but powerful lines of text: “You only have to know one thing: You can learn anything.” And from algebra to astronomy, this resource offers a plethora of free courses on, well, almost anything — for free.

That includes a few video tutorials on Excel. Most of them are part of larger, multi-installment courses on broader topics, like statistics. A general search for “Microsoft Excel” yields what might look like limited results, but they actually explain some fundamental parts of using Excel, like distributions and fitting lines to data.

How to Learn Excel With Paid Training Resources

14. Lynda.com’s Excel Training and Tutorials

Price: Free to try | $19.99/month membership

If you’re willing to invest a little cash in your Excel training, Lynda’s Excel Training and Tutorials are a worthwhile place to spend it. Members of this LinkedIn subsidiary have access to thousands of courses on business, technology, creative skills, and software that’ll help you work toward your personal and professional goals.

Included are over 100 courses on Excel, and over 4,000 video tutorials covering every version of the program, at any level of expertise. They cover a broad range of topics, from something as general as “Statistics with Excel Part One,” to more niche topics, like “Data Visualization Storytelling Essentials.”

15. Coursera

Price: $79/course

While Lynda.com asks for a monthly all-access membership fee, Coursera charges on a course-to-course basis. Partnering with top universities and organizations worldwide, the site offers online classes on a number of topics, ranging from music production to coaching skills.

There are only a few courses pertaining to Excel, but if you’re looking for one that’s on a formally academic level, they could be a good fit for you. In fact, many of the Excel-related courses come from Duke University, such as “Excel to MySQL: Analytic Techniques for Business Specialization.”

That said, these courses don’t come cheap — after all, they’re the same ones that are taught at top universities around the world. And like many real-world classes, each includes video lectures, interactive quizzes, peer-graded assessments, and the opportunity to connect with fellow students and instructors. Once you finish a course, you’ll receive formal recognition, along with an optional course certificate.

16. Udemy

Price: $10.99

If you had six hours to spare, how would you use them? “Sleep,” “clean the house,” and “bake something” are some of the things that come to the top of my mind, but try this on for size — what if you could become an Excel expert in that amount of time?

That’s what Udemy promises in its “Microsoft Excel – From Beginner to Expert in 6 Hours” course — for $10.99. Udemy is one of the most bountiful online learning resources out there, and its Excel courses certainly don’t end with that single option. In fact, when I return to the homepage, it displays several additional lessons on the topic, in case I want to explore my options.

Those options are many. In fact, just typing “Excel” into the search bar yields dozens of results, each one displaying a star rating, price, length, and level.

17. Intellezy.com

Price: Free to try | $14.95 mo.

Big believers in microlearning, Intellezy’s videos fall in the three- to five-minute range. These nuggets of knowledge are organized into beginner through advanced courses, covering Excel 2007-2016 and Office365. Plus, mobile apps for iOS and Android make it easy to learn on the go.

Intellezy’s videos are really engaging. There’s always an instructor on-screen showing you what to do. And because they’re wired for face-to-face interactions, Intellezy’s instructors really hold your attention. Their courses also include assessments and exercise files so you can see how you’re doing and follow along with the instructor. You can give Intellezy a spin with a free 10-day trial.

18. Excel Everest

Price: $159

The name of this resource may look intimidating, but you’d be surprised how convenient the training is.

Excel Everest is an out-of-the-box resource that teaches you Excel while you’re in Excel. The product you buy downloads an Excel file to your computer, where you’ll open Excel and engage in walk-throughs of 41 different Excel functions right from a sample spreadsheet. Topics covered include conditional formatting, VLOOKUP, chart creation, and so much more.

This Excel training resource offers hundreds of exercises, along with video tutorials embedded directly in a sample Excel spreadsheet. By applying these concepts in Excel as you learn them, you’ll feel them stick with you long after you complete Excel Everest’s training.

19. Learn iT!

Price: $130/module

Learn iT! offers Excel training in a variety of formats and breaks every lesson down by module. It’s the best solution for Excel users who might not be receptive to just a video series or written step-by-step guide.

With Learn iT!, Excel users can select from five different courses: Pivot Tables, Intro to Data Analysis, Programming with VBA, Excel Power User, and a basic Excel course for general users. Each course states the course’s duration (in days) and the number of modules the course consists of.

The best part about Learn iT! is that you can take your selected course four different ways: an in-person instructor-led session, live online with classmates, privately in a custom-made session, or a self-paced mix of instructor-led and interactive e-learning courses.

20. LinkedIn Learning Products

Price: Free to try | $29.99 mo.

LinkedIn isn’t just for professional networking. You can also learn a ton on various business topics with the help of subject matter experts (SMEs) who host trainings on the LinkedIn Learning platform. Microsoft Excel is one such topic.

Dennis Taylor, a business consultant you can also learn from on Lynda.com, is one SME who hosts Excel training courses on LinkedIn. His courses range from six hours to 23 hours in total training time, and can be completed on your schedule.

Check out LinkedIn Learning for all available Excel courses.

21. eLearnExcel

Price: Free to try | $47/course

Not only does eLearnExcel give you eight Excel training courses to choose from, but it awards seven Excel certifications as well.

eLearnExcel makes this promise: by taking all of its Excel courses, you’ll have a skill in Excel that’s greater than 99% of Excel users today. Trusted by Microsoft itself, this suite of training products breaks down Excel’s concepts in easy, digestible video modules — allowing you to learn the Excel concepts that are most relevant to you. Courses range from 45 minutes to six hours in duration.

Seven of these courses award you a certification that corresponds with the course you took. If you complete all seven, you’re awarded an Excel Master Diploma. It’s not a bad item to put on your resume — or on your business’s website, if multiple employees enroll in eLearnExcel’s courses as part of a business subscription.

Ready to get started? With these tools, you’ll be using Excel with little-to-no-sweat, in no time. Plus, practice makes perfect — that’s why there are so many tiered levels of courses available. Start where you can, and as you begin using more functions and commands, you can continue to expand your knowledge.

Want more Excel tips? Check out these free Excel templates.

[iyarpage]

Data Persistence With Room

Many apps need to deal with persisting data. Perhaps you have an app that stores your favorite pet photos, a social networking app for cat lovers, or an app to maintain lists of things that you might need for your next vacation.

Android provides a number of options, including:

Shared Preferences: For storing primitive data in key-value pairs.

Internal Storage: For storing private data on device storage.

External Storage: For storing public data on shared external storage.

SQLite Databases: For storing structured data in a private database.

When your data is structured, and you need to be able to do things such as searching for records in that data, a SQLite database is often the best choice. This is where Room comes in. Room is a SQLite wrapper library from Google that removes much of the boilerplate code you need to interact with SQLite, and also adds compile-time checking of your SQL queries.

In this tutorial, you will build an application that creates a generic list that could be used as a shopping, to-do or packing list. In this tutorial you will learn:

The basics of setting up a Room database.

How to use a DAO to Create and Read data.

The basics of unit testing your persistence layer.

How to hook up your database to an Android UI.

Note: This tutorial assumes that you have some experience developing Android applications. A few points to keep in mind:

You will be using the Android RecyclerView to display lists. If you’ve never used them or need a refresher, the Android RecyclerView Tutorial with Kotlin is a great place to start.

This tutorial utilizes Data Binding and Binding Adapters. Again, if you have never used these or need a refresher, you should take a look at the data binding documentation from the Android project pages.

The code snippets in this tutorial do not include the needed import statements. Use the key combination Option-Return on Mac Alt-Enter on PC to resolve any missing dependencies as you work through your project.

Introduction to Android Data Persistence

Classes, Tables, Rows and Instances

To understand Room, it is helpful to understand the sum of its parts, so let’s start with a simple example of storing the names, addresses and phone numbers of a few people.

When you are developing applications using an object-oriented programming language like Kotlin, you use classes to represent the data that you are storing. In our example you could create a class called Person, with the following attributes:

name

address

phoneNumber

For each person you’d then create an instance of a Person, with unique data for that individual.

With a SQL relational database, you would model the Person class as a table. Each instance of that person would be a row in that table. In order to store and retrieve this data, SQL commands need to be be issued to the database, telling it to retrieve and store the data.

For example, to store a record in a table you might use a command like:

INSERT INTO Persons (Name, Address, TelephoneNumber) VALUES ('Grumpy Cat', '1 Tuna Way, Los Angeles CA', '310-867-5309');

In the early days of Android, if you had a Person object that you wanted to store in the SQLite database, you would have had to create glue code that would turn objects into to SQL and SQL into objects.

ORMs and Android

Long before the days of Android, developers in other object-oriented languages started using a class of tool called an ORM to solve this problem. ORM stands for Object Relational Mapper. The best way to think of it is as a tool designed to automatically generate glue code to map between your object instances and rows in your database.

When Android came on the scene, no ORM existed for the Android environment. Over the years, open-source ORM frameworks emerged, including DBFlow, GreenDAO, OrmLite, SugarORM and Active Android. While these solutions have helped to solve the basic problem of reducing glue code, the developer community has never really gravitated towards one (or two) common solutions. This has led to significant fragmentation and limitations in many of these frameworks, especially with more complex application lifecycles.

Google’s Android Architecture Components and Room

Beyond data persistence, a number of patterns have emerged within the Android development community to deal with things such as maintaining state during application lifecycle changes, callbacks, separating application concerns, view models for MVVM applications, etc. Luckily, in 2017, Google took some of the best practices from the community and created a framework called the Android Architecture Components. Included in this framework was a new ORM called Room. With Room you have an ORM to generate your glue code with the backing of the creators of Android.

Getting Started With Room

To kick things off, start by downloading the materials for this tutorial (you can find the link at the top or bottom of this tutorial), unzip it, and start Android Studio 3.1.1 or later.

In the Welcome to Android Studio dialog, select Import project (Eclipse ADT, Gradle, etc.).

Choose the ListMaster directory of the starter project, and click Open.

If you see a message to update the project’s Gradle plugin since you’re using a later version of Android Studio, go ahead and choose “Update”.

Check out the project for the List Master app and you will see two packages for list categories and list items. You’ll be working only in the first package in this tutorial.

Build and run the application and your app will look like this:

Under the Gradle Scripts part of your project you’ll see a build.gradle file with a (Module:app) notation. Double-click to open it and add the following dependencies that add Room to your project.

implementation "android.arch.persistence.room:runtime:1.0.0" kapt "android.arch.persistence.room:compiler:1.0.0" annotationProcessor "android.arch.persistence.room:compiler:1.0.0" androidTestImplementation "android.arch.persistence.room:testing:1.0.0" androidTestImplementation "android.arch.core:core-testing:1.1.0"

Go ahead and sync Gradle files once you’ve made the change.

You now have your Room dependencies. Next, you will need to add the following things to use Room in your app:

Entity: An entity represents the data model that you are mapping to a table in your database.

DAO: short for Data Access Object, an object with methods used to access the database.

Database: A database holder that serves as the main access point for the connection to your database.

Entities

An entity is an object that holds data for you to use in your application with some extra information to tell Room about its structure in the database. To start, you are going to create an Entity to store a category_name and id in a table called list_categories.

Under the com.raywenderlich.listmaster.listcategory package in your app you will see a Kotlin class named ListCategory. Open it by double-clicking and replace the data class with the following code:

@Entity(tableName = "list_categories") data class ListCategory(@ColumnInfo(name="category_name") var categoryName: String, @ColumnInfo(name="id") @PrimaryKey(autoGenerate = true) var id: Long = 0)

The @Entity(tableName = "list_categories") annotation is telling Room that this is an Entity object that is mapped to a table called list_categories. The @ColumnInfo annotation is telling Room about the columns in your table. For example, the name argument in the @ColumnName(name="category_name") annotation tells Room that the data class property categoryName directly after it has a column name of category_name in the table.

DAOs

Now that you have your Entity which contains your data, you are going to need a way to interact it. This is done with a data access object, also referred to as a DAO. To start out, you are going to create a DAO to insert and retrieve records from the table that you’ve created with your Entity.

To do that, right-click the listcategory package in com.reywenderlich.listmaster in your project, select New > Kotlin File/Class, give it a name of ListCategoryDao and press OK. When the editor opens up your new file, paste the following code snippet:

@Dao interface ListCategoryDao { @Query("SELECT * FROM list_categories") fun getAll(): List<ListCategory> @Insert fun insertAll(vararg listCategories: ListCategory) }

The first thing you will notice is that for a DAO you’re creating an interface instead of a class. That is because Room is creating the implementation for you. The @Dao annotation tells Room that this is a Dao interface.

The @Query annotation on getAll() tells room that this function definition represents a query and takes a parameter in the form of a SQL statement. In this case you have a statement that retrieves all of the records in the list_categories table.

Your getAll() method has a return type of List. Under the hood, Room looks at the results of the query and maps any fields that it can to the return type you have specified. In your case, you’re querying for multiple ListCategory entities and have your return value specified as a List of ListCategory items.

If you have a query that returns data that Room is not able to map, Room will print a CURSOR_MISMATCH warning and will continue to set any fields that it’s able to.

Your insertAll method is annotated with @Insert. This tells Room that you are inserting data into the database. Because Room knows about the table and its columns, it takes care of generating the SQL for you.

Database

Now that you have an Entity and a DAO, you’re going to need an object to tie things together. This is what the Database object does. Under the com.reywenderlich.listmaster package in your app, create a new Kotlin File/Class called AppDatabase and paste in the following code:

@Database(entities = [(ListCategory::class)], version = 1) abstract class AppDatabase : RoomDatabase() { abstract fun listCategoryDao(): ListCategoryDao }

You tell Room that the class is a Database object using the @Database annotation. The entities parameter tells your database which entities are associated with that database. There is also a version that is set to 1. The database version will need to be changed when performing a database migration, which will be covered in a later tutorial.

Your database class can be named whatever you want it to be, but it needs to be abstract, and it needs to extend RoomDatabase. All of your DAOs need to have abstract methods that return the corresponding DAO. This is how Room associates a DAO with a database.

Database Instances

In order to use your database, you’re going to need to create an instance of it in your application. Although you only needed to create a few classes, behind the scenes, Room is doing a lot of work for you to manage, map and generate SQL. Because of this, an instance of Room is resource-intensive, which is why its creators recommend that you use a singleton pattern when when using it in your application.

An easy way to achieve that is by creating an instance of the database when the application is loaded, and then referencing it at the Application level. Go to the com.reywenderlich.listmaster package, open ListMasterApplication by double clicking, and replace the class with the following code:

class ListMasterApplication: Application() { companion object { var database: AppDatabase? = null } override fun onCreate() { super.onCreate() ListMasterApplication.database = Room.databaseBuilder(this, AppDatabase::class.java, "list-master-db").build() } }

When looking at the call to create your database instance you’ll notice that three parameters are passed in. A reference to the app context, a reference to your database class, and a name. Behind the scenes, this name corresponds to a filename that is used to store your database in your apps’ internal storage.

Testing the Essence of your Database with Espresso

When you think about espresso, your first thought may be about about a machine creating a delicious brown nectar that gives you energy during the day. :]

While the happiness and energy from a good cup of espresso are always helpful when you’re developing Android applications, in this case, we are talking about an Android testing framework called Espresso.

Now, to test your database, you could write some code in your activity that inserts data and then prints out the results. You could also begin to wire it to a UI. But, this can be treated as a separate testable piece of the application to make sure your database is set up correctly.

If you haven’t worked with Espresso or unit testing before, don’t worry, we’ll guide you through what you need test your database with it. So grab your cup of espresso and let’s get testing!

In your project, there’s a com.raywenderlich.listmaster package with (androidTest) next to it.

Under that package, there’s a Kotlin file called ListCategoryDaoTest. Open it by double-clicking and replace the contents with the following code:

@RunWith(AndroidJUnit4::class) class ListCategoryDaoTest { @Rule @JvmField val rule: TestRule = InstantTaskExecutorRule() private lateinit var database: AppDatabase private lateinit var listCategoryDao: ListCategoryDao @Before fun setup() { val context: Context = InstrumentationRegistry.getTargetContext() try { database = Room.inMemoryDatabaseBuilder(context, AppDatabase::class.java) .allowMainThreadQueries().build() } catch (e: Exception) { Log.i("test", e.message) } listCategoryDao = database.listCategoryDao() } @After fun tearDown() { database.close() } }

So, what is going on here? You created database and DAO properties for the test class. In your setup() method, you are creating an in-memory version of our database using Room.inMemoryDatabaseBuilder and getting a reference to your listCategoryDao. You close the database in tearDown().

Now, using the magic of testing you’re going to create some queries and use asserts to see what is going on. Add the following test into the test class:

@Test fun testAddingAndRetrievingData() { // 1 val preInsertRetrievedCategories = listCategoryDao.getAll() // 2 val listCategory = ListCategory("Cats", 1) listCategoryDao.insertAll(listCategory) //3 val postInsertRetrievedCategories = listCategoryDao.getAll() val sizeDifference = postInsertRetrievedCategories.size - preInsertRetrievedCategories.size Assert.assertEquals(1, sizeDifference) val retrievedCategory = postInsertRetrievedCategories.last() Assert.assertEquals("Cats", retrievedCategory.categoryName) }

Your test is doing the following:

First, your test calls the getAll() method on your DAO that will get all of the current records in your database.

Second, you create an entity object and insert it into your database.

Finally, you perform some Assert.assertEquals calls on the result set after the record is added to ensure that a record was added. To do that, you are comparing the size before and after adding a record to make sure that the difference is 1, and then you look at the last record to ensure that its elements match the record you added.

When you run an Espresso test, it installs your application on a device or emulator and then runs all of the code in your tests. Under the com.raywenderlich.listmaster package with the (androidTest) notation, right-click your ListCategoryDaoTest file and select Run ‘ListCategoryDaoTest’. You can also click the green arrow next to the test class name, or click the arrow next to an individual test.

When it asks you to select a deployment target, select your favorite emulator or device.

Your project will build, install, and the tests will run. The bottom of your Android Studio window should look like this:

Wiring Up Your Interface

While a unit test against your database is a good start, when you try to run the application, you may feel like this…

To get beyond that, you’d need to have an interface to interact with your database. Your sample project already has the skeleton elements for your applications layout, colors etc. You’re going to hook up some queries to read from and write to your database.

To make that process smoother, you’ll use data binding to feed that data into and out of the interface. Your list will also be using a Recycler View.

When you’re finished, you’ll have an app that shows a list of categories. Each row will have a circle that has the first letter of the category on the left, with one of six colors based on a hash code of the letter.

To begin, go to the listcategory package under com.raywenderlich.listmaster, and double-click the file called ListCategoryViewModel. You will see the following code:

data class ListCategoryViewModel(val listCategory: ListCategory = ListCategory("")) { private val highlightColors = arrayOf(R.color.colorPrimary, R.color.colorPrimaryDark, R.color.colorAccent, R.color.primaryLightColor, R.color.secondaryLightColor, R.color.secondaryDarkColor) fun getHighlightLetter(): String { return listCategory.categoryName.first().toString() } fun getHighlightLetterColor(): Int { val uniqueIdMultiplier = getHighlightLetter().hashCode().div(6) val colorArrayIndex = getHighlightLetter().hashCode() - (uniqueIdMultiplier * 6) Log.i("color", colorArrayIndex.toString()) return (highlightColors[colorArrayIndex]) } }

In this code, you’re using a standard data class and adding some extra logic for setting up the circle and highlight letter. The highlight tint is hooked in using the adapter in your DataBindingAdapters.kt file. You’re going to display your list in a RecyclerView, which consists of a ViewHolder and Adapter that you will initialize in your Activity.

To use the adapter, you’ll need to a layout for each item. Your starter project already has one. To take a look at it, open the file under the res/layout part of your project called holder_list_category_item.xml, a snippet of which is shown here:

<data> <variable name="listCategoryItem" type="com.raywenderlich.listmaster.listcategory.ListCategoryViewModel" /> </data> <android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="wrap_content" tools:layout_editor_absoluteX="8dp"> <TextView android:id="@+id/category_header" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_marginBottom="16dp" android:layout_marginEnd="8dp" android:layout_marginStart="16dp" android:layout_marginTop="16dp" android:background="@drawable/circle" android:gravity="center" android:highlight_tint="@{listCategoryItem.highlightLetterColor}" android:text="@{listCategoryItem.highlightLetter}" android:textAlignment="center" android:textColor="@android:color/white" android:textSize="20sp" app:layout_constraintBottom_toBottomOf="parent" app:layout_constraintEnd_toStartOf="@+id/category_name" app:layout_constraintHorizontal_chainStyle="spread" app:layout_constraintHorizontal_weight=".15" app:layout_constraintStart_toStartOf="parent" app:layout_constraintTop_toTopOf="parent" />

The listCategoryItem in the data tag is your bound View Model. The various @{...} items in the text view attribute values make use of the data in the View Model. You will also notice a text view attribute called highlight_tint. This is a custom field that was added via a BindingAdapter in the file named DataBindingAdapters.kt under com.raywenderlich.listmaster in your starter package.

Your starter project already has a ListCategoryAdapter and ListCategoryViewHolder under the listcategory package.

In order to display your list, you’re going to need a RecyclerView for the ListCategoriesActivity. Luckily your starter project has that in the content_list_categories.xml layout, which is included under the res/layout section of your project.

<?xml version="1.0" encoding="utf-8"?> <layout> <android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" app:layout_behavior="@string/appbar_scrolling_view_behavior" tools:context="com.raywenderlich.listmaster.ListCategoriesActivity" tools:showIn="@layout/activity_list_categories"> <android.support.v7.widget.RecyclerView android:id="@+id/list_category_recycler_view" android:layout_width="match_parent" android:layout_height="match_parent" /> </android.support.constraint.ConstraintLayout> </layout>

Now it’s time to wire up your activity. To do that, you will need to add a few things to ListCategoriesActivity. Go to the listcategory package under com.raywenderlich.listmaster and open ListCategoriesActivity by double-clicking.

To start, paste in the following properties near the top of the class:

private lateinit var contentListsBinding: ContentListCategoriesBinding private lateinit var appDatabase: AppDatabase private lateinit var listCategoryDao: ListCategoryDao

The first property will be generated by the data binding API when the project is built. The other properties are for references to the database and the list category DAO. If necessary, add the following import to your import statments:

import com.raywenderlich.listmaster.databinding.ContentListCategoriesBinding

Next, add the following function to the bottom of the activity:

private fun setupRecyclerAdapter() { val recyclerViewLinearLayoutManager = LinearLayoutManager(this) contentListsBinding = activityListsBinding.contentLists!! contentListsBinding.listCategoryRecyclerView.layoutManager = recyclerViewLinearLayoutManager listCategoryAdapter = ListCategoryAdapter(listOf(), this) contentListsBinding.listCategoryRecyclerView.adapter = listCategoryAdapter }

As its name suggests, this function sets up the recycler view. In order to initialize this before you have a list of items from the database, an empty list is passed to its adapter.

Now that you have these building blocks, paste the snippet below right after the setupAddButton() call in your onCreate() method:

// Set up our database appDatabase = ListMasterApplication.database!! listCategoryDao = appDatabase.listCategoryDao() // Set up our recycler adapter setupRecyclerAdapter()

This is getting references to your database and listCategoryDao for use in your activity, along with calling your setupRecyclerAdapter() method.

Next, add code to query Room for the list of categories and add them to the adapter by pasting the onResume method into your ListCategoriesActivity class:

override fun onResume() { super.onResume() AsyncTask.execute({ listCategoryAdapter.categoryList = listCategoryDao.getAll() runOnUiThread { listCategoryAdapter.notifyDataSetChanged() } }) }

You may have noticed that this call the the DAO is run in a AsyncTask. This is because these database calls are reaching out to your file system and could take a while to complete, especially if you have a complex query. A long query can cause the UI to freeze or even produce a dreaded Application Not Responding error. Since both of these things can lead to unhappy users, it’s best to always put these calls in a background thread.

Now that you have your list query wired in, you’re going to need to have a way to add in category items. To do that, your starter project has a dialog layout in a file called dialog_add_category.xml that you are going to hook up to the Add button. Find the function called setupAddButton in your ListCategoriesActivity and update the call to setPositiveButton on alertDialogBuilder to look as follows:

alertDialogBuilder.setPositiveButton(android.R.string.ok, { dialog: DialogInterface, which: Int -> AsyncTask.execute({ listCategoryDao.insertAll(listCategoryViewModel.listCategory) listCategoryAdapter.categoryList = listCategoryDao.getAll() runOnUiThread { listCategoryAdapter.notifyDataSetChanged() } }) })

You insert the new category from the dialog into the database, and then update your list of categories on the adapter and refresh the adapter on the UI thread.

Now it’s time to run your application! Go to the Run menu and select the Run option. Note: If you select ListCategoryDaoTest instead, it will run your tests instead of your app.

Next, select the App option:

Then, select a device to run it on:

Your app will run and you’ll be able to add categories to your list.

Go ahead and add a few categories. Then stop the app and build and run again.

You see that your categories have been persisted in SQLite thanks to Room! :]

Where To Go From Here?

You can download the final project using the link at the top or bottom of this tutorial.

There’s a lot more to learn and do with Room!

Migrations for making changes to an existing data store.

Indexes to make your queries faster.

Type Converters to make it easier to map database types to your own custom types.

Stay tuned for future Room tutorials where these will be covered. :]

If you’re just getting started with data persistence on Android and want some more background, you can check out our Saving Data on Android video course, which covers SharedPreferences, saving to files, SQLite and migrations, and also persisting using Room along with other Android Architecture Components such as ViewModel and LiveData.

As a challenge, you can also try to:

Add the ability to delete a list category.

Create a onClick event in each category that allows you to edit the category and save it to your database.

Feel free to share your feedback, findings or ask any questions in the comments below or in the forums. I hope you enjoyed this tutorial on data persistence with Room!

The post Data Persistence With Room appeared first on Ray Wenderlich.

Data Persistence With Room published first on https://medium.com/@koresol

[readersforum]

How to Learn Excel Online: 21 Free and Paid Resources for Excel Training

New Post has been published on http://www.readersforum.tk/how-to-learn-excel-online-21-free-and-paid-resources-for-excel-training/

How to Learn Excel Online: 21 Free and Paid Resources for Excel Training

Like many marketers, I have a bit of experience with Microsoft Excel. I’ve used it to organize events, plan meals, and sort data — but I don’t have nearly the advanced knowledge I wish I did.

And thanks to those limited skills, I’m constantly subjecting myself to the tedium of updating my spreadsheets manually.

Like me, you’re missing out on a world of Excel training courses that could teach me how to automate my reports and save hours of time.

When I asked even my most Excel-savvy colleagues where they picked up their knowledge, they told me things like, “I mostly learned from colleagues and friends,” or, “When I have a specific question, I ask someone or search on Google.” Fair enough. But as a beginner, I probably have a few too many Excel questions to rely on colleagues — or Google — to answer every one.

I can’t be the only one out there who wants to master the world’s most popular data analysis and visualization solution — or at least learn how to create charts and graphs that’ll impress my manager.

So in the spirit of becoming a more productive, data-driven marketer, I scoured the internet for the best online resources for learning Excel. Half of them are free, and the ones that aren’t might be worth the investment. In these trainings, you’ll learn the following:

Learning Excel

Create PivotTables to find relationships between data.

Enter formulas across cells, rows, and columns.

Conduct a VLOOKUP across an entire column.

Run accounting functions to track business finances.

Group, ungroup, and reformat rows and columns.

Perform data validation to control the format of cell values.

Use keyboard shortcuts to make quick changes to cell values.

Create charts from your spreadsheet data.

Develop histograms of data from the charts you create.

Create dashboards to manage multiple groups of performance data.

Add conditional formatting to rows and columns that fit certain criteria.

Print your Excel spreadsheet for presenting to others.

Take a look at the free and paid resources below. Bookmark your favorites and get that much closer to working more efficiently in Excel.

How to Learn Excel With Free Training Resources

1. Microsoft’s Excel Training Center

When it comes to learning a new application, why not start at the source? After all, no one knows Excel better than the people at Microsoft.

In fact, they’ve done a great job putting together the Office Training Center: A resource hub for all Microsoft Office applications and services. The training center for Excel has a whole bunch of free tutorials, videos, and guides on Windows, Mac OS, Android, iOS, and Windows Phone that cover the latest version of Excel, as well as older ones.

Once you click into a platform, you’ll find resources divided by Excel ability: For beginners (like basic math and creating a chart), intermediate users (like sorting and filtering data, conditional formatting, and VLOOKUPs), and advanced users (like pivot tables, advanced IF functions, and how to password-protect worksheets and workbooks).

Source: Microsoft

2. Excel Exposure

Excel Exposure is a free series of written and video-based courses to help you learn Excel regardless of your skill level. There are nearly 40 lessons — split into “Beginner,” “Intermediate,” and “Advanced” — each no more than 20 minutes long.

In addition to this a-la-carte curriculum, Excel Exposure offers a “Master Workbook” that lives directly in Excel to help you practice what you learn in each lesson. Learnings include data and time functions, how to group and ungroup rows and columns, using conditional formatting, PivotTables, chart design, and more.

3. HubSpot Excel Resources

Seeing as Excel is one of the most in-demand skills for data-driven marketers — and because we want marketers like you to succeed — we’ve created some of our own educational content about Excel here at HubSpot. From free ebooks, to templates, to video tutorials, we aim to cover a wide range of Excel-relevant topics.

Here are a few of our best:

“How to Use Excel: The Essential Training Guide for Data-Driven Marketing” (free ebook)

“9 Excel Templates to Make Marketing Easier” (free templates)

“How to Create a Pivot Table in Excel: A Step-by-Step Tutorial” (video & blog post)

4. The Spreadsheet Page

Here’s a very well-organized site that’s chock full of helpful Excel tips, collected by an expert named John Walkenbach. Over the past 30 years, he’s written more than 60 Excel books for users of all levels, and around 300 articles and reviews for magazines like InfoWorld, PC World, and PC/Computing. At one point, he wrote the monthly spreadsheet column for PC World. In other words, the man knows his stuff — and he knows how to present it.

The most helpful part of his website is probably the Excel Tips tab, which has a long list of useful pointers on formatting, formulas, charts and graphics, and printing. The tips themselves include everything from working with fractions, to unlinking a pivot table from its source data, to spreadsheet protection FAQs.

The Downloads tab is another particularly helpful section of Walkenbach’s site, where he’s added free, ungated download links to files he created, like free Excel workbooks and add-ins. For example, there’s one Excel workbook available for download that gives examples of custom number formats, which you can play with and tweak on your own time, and get familiar with them without having to start from scratch.

5. About.com’s Spreadsheets Page

Many of you are likely familiar with the content website About.com, but did you know it has its own spreadsheets subdomain — much of which is devoted to Excel? There are likely thousands of instruction sets on that site, most of which are illustrated, how-to posts. Plus, fresh content is added regularly.

Each piece of content is categorized according to everything from formulas and formatting, to videos, tools, and templates. If you want to stay up-to-date on the latest spreadsheet news and tips, you can sign up for a free newsletter. There’s just one caveat: the site contains a good amount of ads — but if you can stand them, the content is worth it.

6. Contextures

Contextures is a place for free Excel instructions in the form of quick tips, blog posts, a newsletter, and even .zip files you can download to see how various functions work in the context of real tables of data.

This resource has literally hundreds of downloadable spreadsheets that open in Excel and offer step-by-step instructions for performing data validation, adding filters to rows and columns, adding conditional formatting to rows and columns, designing charts, creating PivotTables, running functions, and more.

7. Chandoo.org

Purna “Chandoo” Duggirala, Chandoo.org‘s founder, says he has one goal: “to make you awesome at Excel and charting.” He started the blog in 2007 and, today, it contains more than 450 articles and tutorials on using Excel and making better charts. He’s built the blog as a community, citing values like humility, passion, fun, and simplicity.

He also works to make it a valuable resource for the folks for whom English is not their first language.

Most of his tips stem from forums, where people ask questions about Excel — about formulas, formatting, shortcuts, pivot tables, and so on — and anyone can answer them. Chandoo then uses some of the more helpful forum questions to create articles and tutorials.

Source: Chandoo.org

But it’s not all so formal. For example, Chandoo once created a digital Easter egg hunt for a blog post, which included a downloadable Excel workbook containing seven hidden pandas. Readers were challenged to locate the pandas using clues, Excel techniques, and even “I-Spy” skills.

Source: Chandoo.org

While the articles, forums, and other parts of the site are free, you can pay to join one of Chandoo’s structured training programs, like Excel School ($97 – $247), or VBA Classes ($97 – $347). Plus, there’s aways the option to buy one of his books — The VLOOKUP Book or Excel Formula Helper Ebook.

8. Excel Easy

Excel Easy is a comprehensive tutorial for learning Excel, breaking down its learnings into chapters. Those chapters are sorted into the following sections:

Introduction

Basics

Functions

Data Analysis

VBA

Examples

In each chapter, Excel Easy walks you through what it perceives to be the fundamentals of Excel, in the form of written guides with screenshots to help you master each concept. At the end of the tutorial, you have access too 300 examples showing you how to perform each concept in real scenarios. Concepts include basic formulas and functions, how to create a ribbon, creating multiple worksheets, formatting cells, data validation, table creation, and more.

9. MrExcel.com

Here’s a resource that puts we mere mortals in touch with Excel experts. MrExcel.com‘s claim to fame is its interactive message board, which is constantly monitored by its community of Excel gurus.

The board is organized according to subject, like general announcements, questions, and MrExcel.com products. When a user posts a question, a member of the MrExcel.com expert community will reply with an answer. The questions range from simplifying an Excel task, to solving urgent inquiries.

Not a native English speaker? You can ask questions in your native language.

Aside from posting questions on the message board, you can also browse Mr. Excel’s “Hot Topics” — found on the left-hand side of its homepage — which includes things like finding the cumulative sum of even or odd rows, or removing the leading zero within a text field. The site also has a library of helpful Excel books and ebooks, and if you need help with problems that are more complex, you can even hire an Excel consultant directly from the website, for a fee.

10. Exceljet

Exceljet offers hundreds of free formula walk-throughs, videos, and blog posts that allow you to itemize your Excel training based on the project you’re working on. It also has several paid programs that take a deeper dive into specific Excel topics its users — of which there are more than 10,000 — are interested in.

11. EdX Excel Courses

Here’s a budget-friendly option for those in search of a more formal course, rather than a one-off tutorial: EdX is a nonprofit that provides free education for people around the globe — with an interesting model of Excel training sessions, both timed and self-paced.

When users enroll in a course that’s marked as “Verified,” they have the option to pay a fee in exchange for an instructor-signed certificate with the institution’s logo, to verify the achievement and increase job prospects. Those fees are used to fund the courses, giving you the option to take them for free if you don’t mind foregoing the certificate.

Otherwise, there are some courses offered at a “Professional Education” level, for which the fee isn’t optional. One example is the Business and Data Analysis Skills course, offered for $60.

To help you choose the right one, each edX course includes reviews (with a rating up to five stars), and information on length and amount of effort, usually measured in hours per week. There are also details on the level of knowledge required, along with video transcripts.

12. Annielytics Video Tutorials

Annie Cushing, a web analytics data expert, created the Annielytics blog and YouTube channel to share her knowledge with the world. Don’t let the punny name fool you — both are chock full of really good, specific, and in-depth web analytics tips.

While the content here isn’t all Excel-related — much of it is about Google Analytics, for example — it does contain some great Excel video tutorials. Even better, they were created with marketing and web analytics in mind, so they’re directly applicable to things like marketing data reports. The Excel-specific videos can be found here, or by searching her YouTube channel for “Excel”.

The Excel topics vary widely, from how to create interactive pivot tables, to how to add a scrolling table to your dashboard using the INDEX function. The videos also vary in length depending on topic complexity, ranging from two-and-a-half-minutes, to those over half an hour long. To give you an idea of what the videos are like, here’s one of our favorites, which covers a comprehensive overview of Excel charts:

13. Khan Academy

When visitors arrive at the Khan Academy website, they’re greeted with two simple but powerful lines of text: “You only have to know one thing: You can learn anything.” And from algebra to astronomy, this resource offers a plethora of free courses on, well, almost anything — for free.

That includes a few video tutorials on Excel. Most of them are part of larger, multi-installment courses on broader topics, like statistics. A general search for “Microsoft Excel” yields what might look like limited results, but they actually explain some fundamental parts of using Excel, like distributions and fitting lines to data.

How to Learn Excel With Paid Training Resources

14. Lynda.com’s Excel Training and Tutorials

Price: Free to try | $19.99/month membership

If you’re willing to invest a little cash in your Excel training, Lynda’s Excel Training and Tutorials are a worthwhile place to spend it. Members of this LinkedIn subsidiary have access to thousands of courses on business, technology, creative skills, and software that’ll help you work toward your personal and professional goals.

Included are over 100 courses on Excel, and over 4,000 video tutorials covering every version of the program, at any level of expertise. They cover a broad range of topics, from something as general as “Statistics with Excel Part One,” to more niche topics, like “Data Visualization Storytelling Essentials.”

15. Coursera

Price: $79/course

While Lynda.com asks for a monthly all-access membership fee, Coursera charges on a course-to-course basis. Partnering with top universities and organizations worldwide, the site offers online classes on a number of topics, ranging from music production to coaching skills.

There are only a few courses pertaining to Excel, but if you’re looking for one that’s on a formally academic level, they could be a good fit for you. In fact, many of the Excel-related courses come from Duke University, such as “Excel to MySQL: Analytic Techniques for Business Specialization.”

That said, these courses don’t come cheap — after all, they’re the same ones that are taught at top universities around the world. And like many real-world classes, each includes video lectures, interactive quizzes, peer-graded assessments, and the opportunity to connect with fellow students and instructors. Once you finish a course, you’ll receive formal recognition, along with an optional course certificate.

16. Udemy

Price: $10.99

If you had six hours to spare, how would you use them? “Sleep,” “clean the house,” and “bake something” are some of the things that come to the top of my mind, but try this on for size — what if you could become an Excel expert in that amount of time?

That’s what Udemy promises in its “Microsoft Excel – From Beginner to Expert in 6 Hours” course — for $10.99. Udemy is one of the most bountiful online learning resources out there, and its Excel courses certainly don’t end with that single option. In fact, when I return to the homepage, it displays several additional lessons on the topic, in case I want to explore my options.

Those options are many. In fact, just typing “Excel” into the search bar yields dozens of results, each one displaying a star rating, price, length, and level.

17. Intellezy.com

Price: Free to try | $14.95 mo.

Big believers in microlearning, Intellezy’s videos fall in the three- to five-minute range. These nuggets of knowledge are organized into beginner through advanced courses, covering Excel 2007-2016 and Office365. Plus, mobile apps for iOS and Android make it easy to learn on the go.

Intellezy’s videos are really engaging. There’s always an instructor on-screen showing you what to do. And because they’re wired for face-to-face interactions, Intellezy’s instructors really hold your attention. Their courses also include assessments and exercise files so you can see how you’re doing and follow along with the instructor. You can give Intellezy a spin with a free 10-day trial.

18. Excel Everest

Price: $159

The name of this resource may look intimidating, but you’d be surprised how convenient the training is.

Excel Everest is an out-of-the-box resource that teaches you Excel while you’re in Excel. The product you buy downloads an Excel file to your computer, where you’ll open Excel and engage in walk-throughs of 41 different Excel functions right from a sample spreadsheet. Topics covered include conditional formatting, VLOOKUP, chart creation, and so much more.

This Excel training resource offers hundreds of exercises, along with video tutorials embedded directly in a sample Excel spreadsheet. By applying these concepts in Excel as you learn them, you’ll feel them stick with you long after you complete Excel Everest’s training.

19. Learn iT!

Price: $130/module

Learn iT! offers Excel training in a variety of formats and breaks every lesson down by module. It’s the best solution for Excel users who might not be receptive to just a video series or written step-by-step guide.

With Learn iT!, Excel users can select from five different courses: Pivot Tables, Intro to Data Analysis, Programming with VBA, Excel Power User, and a basic Excel course for general users. Each course states the course’s duration (in days) and the number of modules the course consists of.

The best part about Learn iT! is that you can take your selected course four different ways: an in-person instructor-led session, live online with classmates, privately in a custom-made session, or a self-paced mix of instructor-led and interactive e-learning courses.

20. LinkedIn Learning Products

Price: Free to try | $29.99 mo.

LinkedIn isn’t just for professional networking. You can also learn a ton on various business topics with the help of subject matter experts (SMEs) who host trainings on the LinkedIn Learning platform. Microsoft Excel is one such topic.

Dennis Taylor, a business consultant you can also learn from on Lynda.com, is one SME who hosts Excel training courses on LinkedIn. His courses range from six hours to 23 hours in total training time, and can be completed on your schedule.

Check out LinkedIn Learning for all available Excel courses.

21. eLearnExcel

Price: Free to try | $47/course

Not only does eLearnExcel give you eight Excel training courses to choose from, but it awards seven Excel certifications as well.

eLearnExcel makes this promise: by taking all of its Excel courses, you’ll have a skill in Excel that’s greater than 99% of Excel users today. Trusted by Microsoft itself, this suite of training products breaks down Excel’s concepts in easy, digestible video modules — allowing you to learn the Excel concepts that are most relevant to you. Courses range from 45 minutes to six hours in duration.

Seven of these courses award you a certification that corresponds with the course you took. If you complete all seven, you’re awarded an Excel Master Diploma. It’s not a bad item to put on your resume — or on your business’s website, if multiple employees enroll in eLearnExcel’s courses as part of a business subscription.

Ready to get started? With these tools, you’ll be using Excel with little-to-no-sweat, in no time. Plus, practice makes perfect — that’s why there are so many tiered levels of courses available. Start where you can, and as you begin using more functions and commands, you can continue to expand your knowledge.

Want more Excel tips? Check out these free Excel templates.